i PL/pgSQL
Tomasz Borzyszkowski
Wielokrotnie już korzystaliśmy z operatorów w klauzulach WHERE, czy HAVING instrukcji SELECT. Np. aby ograniczyć wybór wierszy z tabeli towarów, do wierszy, które spełniają warunek: cena zakupu towaru jest większa niż 4, można zastosować operator > w następujący sposób:
SELECT * FROM item WHERE cost_price > 4;
Operatory można również składać ze sobą uzyskując bardziej
złożone warunki. Np. towary, których cena sprzedaży kończy się na 99/100, można uzyskać następująco:
SELECT * FROM item WHERE (sell_price*100)%100 = 99;
PostgreSQL pozwala także wykonywać skomplikowane operacje na napisach. Przykładem może być zapytanie:
SELECT * FROM item WHERE description ~* '^[PR].*E$;
Wybierające towary, których opisy rozpoczynają się na P, p, R lub r i kończą się na E lub e.
2
Operatory posiadają priorytety, które są na stałe zakodowane w analizatorze składni. Priorytety te decydują o kolejności, w jakiej są wykonywane działania w złożonych wyrażeniach. Jak w innych językach programowania, kolejność ta może być zmieniana za
pomocą nawiasów. Np.:
SELECT 1+2*4;
oraz SELECT (1+2)*4;
Operatory w PostgreSQL charakteryzują się kierunkiem nadawania wartości. Może on być prawy lub lewy. Określa on kolejność
nadawania wartości operatorom o tym samym priorytecie.
Operatory arytmetyczne, jak dodawanie lub odejmowanie, są
wartościowane od lewej do prawej, np.: 1+2-3 jest równoważne (1+2)-3. Inne operatory, jak np. operator równości, są wartościowane od prawej do lewej. Wartość wyrażenia x=y=z jest równa wartości wyrażenia x=(y=z).
Opis najpopularniejszych operatorów wraz z przykładami ich użycia można znaleźć w pliku: operatory.sql
3
Zobacz także: dziwne.sql
PL/pgSQL przegląd Zobacz: add_one.sql Język PL/pgSQL jest ładowalnym językiem proceduralnym, który:
Umożliwia tworzenie funkcji i procedur wyzwalanych ( triggerów)
Rozszerza SQL o struktury sterujące, podobne jak w językach proceduralnych
PL/pgSQL daje możliwość definiowania własnych funkcji, które można wykorzystywać wewnątrz bazy PostgreSQL. Instrukcja
CREATE FUNCTION służy do definiowania nowych funkcji.
Postać ogólna:
CREATE FUNCTION nazwa (typ_arg1[,...])
RETURNS typ_wyniku
AS definicja_funkcji
LANGUAGE 'nazwa jezyka';
Przykład: CREATE FUNCTION add_one (int4)
RETURNS int4 AS '
BEGIN RETURN $1 + 1 END
' LANGUAGE 'plpgsql';
4
Po utworzeniu funkcji, jej definicja jest zapisywana w bazie danych.
Kiedy funkcja jest wywoływana po raz pierwszy, definicja funkcji jest kompilowana przez program obsługi do postaci wykonywalnej, a następnie wykonana. Oznacza to, że możemy nie wiedzieć o błędzie funkcji do czasu, kiedy spróbujemy jej użyć.
Każda baza danych może posiadać zdefiniowane dla niej języki programowania, niezależnie od pozostałych baz (bezpieczeństwo).
Domyślnie bazy danych PostgreSQL nie obsługują żadnych języków proceduralnych. Aby korzystać z języka PL/pgSQL należy
samodzielnie zainstalować program jego obsługi, np. tak:
createlang plpgsql template1;
Administrator bazy danych może dodać języki do bazy danych
template1. W takim przypadku, wszystkie nowe bazy danych będą posiadały domyślną obsługę tych języków.
5
PL/pgSQL argumenty i deklaracje
PL/pgSQL to język blokowo-strukturalny, podobny do języka Pascal lub C, z deklaracjami zmiennych i zakresami bloków. Każdy blok składa się z opcjonalnej etykiety, deklaracji zmiennych i zamykają go instrukcje otoczone słowami kluczowymi BEGIN i END. Składnia:
[ etykieta]
Zobacz: deklar.sql
[DECLARE deklaracje]
BEGIN instrukcje END;
Wielkość liter w tym języku nie ma znaczenia. Wszystkie słowa kuczowe i nazwy zmiennych można zapisać z zastosowaniem liter małych lub wielkich.
Funkcje PL/pgSQL mogą nie mieć argumentów lub mieć ich kilka.
Typy parametrów podaje się w nawiasach po nazwie funkcji. Każda funkcja przechowywana w bazie musi zwracać wynik. Odwołania do parametrów wewnątrz treści funkcji mają postać $1, $2, ... , w kolejności ich definiowania. W części deklaracji można parametrom nadawać nazwy, np.: nazwa ALIAS FOR $5;
6
PL/pgSQL zmienne Zobacz: zmienne.sql Prosta deklaracja zmiennej jest następująca:
nazwa [CONSTANT] typ [NOT NULL] [:= wartosc]; Występowanie CONSTANT oznacza, że wartości zmiennej nie wolno zmieniać. Musi ona posiadać wartość początkową. NOT NULL
powoduje zgłoszenie błędu, gdy zmiennej przypiszemy NULL.
PostgreSQL pozwala deklarować tzw. zmienne złożone,
odpowiadające całym wierszom w określonej tabeli. Składnia:
nazwa tabela%ROWTYPE;
ROWTYPE jest słowem kluczowym, tabela jest nazwą tabeli.
Wynikiem tej deklaracji jest zmienna zawierająca pola, po jednym dla każdej kolumny tabeli, na podstawie której powstała. Aby skorzystać z pól, należy zastosować składnię: zmienna.pole.
Innym rodzajem typu złożonego jest RECORD. Jest on podobny do ROWTYPE ale nie opiera się on na konkretnej tabeli. Typ ten jest wypełniany wartościami w czasie wykonania. Przydatny w
definicjach procedur wyzwalanych (więcej o tym, później).
7
PL/pgSQL instrukcje przypisania
Podstawowa instrukcja przypisania w PL/pgSQLu ma następującą postać:
referencja := wyrazenie;
referencja jest nazwą zmiennej lub pola typu złożonego, np.
ROWTYPE lub RECORD. wyrazenie jest zbudowane z dostępnych w PosgreSQLu operacji, stałych, funkcji, zmiennych, konwersji typów.
W PL/pgSQLu istnieje możliwość przypisywania wartości całym wierszom danych wartości. Służy do tego instrukcja:
SELECT wyrazenia INTO cel [FROM ... ]; W przypadku różnic typów, tam, gdzie to możliwe, PostgreSQL
zastosuje odpowiednie operacje konwersji. Wszystkie operacje przypisania, nawet :=, są wykonywane przez serwer jako powyższa instrukcja SELECT. Przez wykorzystanie klauzuli FROM, ... , możemy zmiennym przypisać wartości obliczone na podstawie
zawartości bazy. Tylko pierwszy wiersz oddawany przez SELECT
jest przypisywany zmiennej, reszta jest pomijana. Jeżeli SELECT
nie zwraca żadnych wierszy, to zmienna FOUND = false.
8
Zobacz: przypisania.sql
Funkcja w PL/pgSQLu, prócz instrukcji RETURN, może także zakończyć się w przypadku powstania warunków, które
uniemożliwiają kontynuowanie jej działania. Zamiast wartości funkcja zwraca wówczas wyjątek. Wyjątek spowoduje wykonanie zapisu w dzienniku zdarzeń i może spowodować, że funkcja
zakończy się natychmiast:
RAISE poziom ' format' [ zmienna, ...]; PostgreSQL definiuje następujące trzy poziomy ważności wyjątków:
DEBUG
zapisuje komunikat w dzienniku (zwykle wyłączone)
NOTICE
zapisuje komunikat w dzienniku i wysyła do aplikacji
EXCEPTION zapisuje komunikat w dzienniku i kończy funkcję Poziom DEBUG stosowany do uzyskiwania dodatkowych informacji podczas normalnego działania funkcji. NOTICE umożliwia generowanie ostrzeżeń dla błędów, które nie są błędami fatalnymi.
EXCEPTION stosuje się dla błędów fatalnych. Więcej w dokumentacji.
Zobacz: wyjatki.sql 9
W PL/pgSQLu istnieje kilka rodzajów instrukcji warunkowych. Są to konstrukcje, które wykonują jeden lub kilka zbiorów instrukcji z w zależności od wyniku testu. Najpopularniejsza jest IF_THEN_ELSE, taka jak na językach programowania:
IF warunek THEN instrukcje
[ELSE instrukcje] END IF;
Kolejne to właściwie wyrażenia warunkowe, a nie instrukcje: NULLIF( wejscie, wartosc);
Wynikiem jest NULL, jeżeli wejscie=wartosc, wejscie w przeciwnym przypadku.
Następna, to dobrze znana instrukcja CASE:
CASE WHEN warunek THEN wyrazenie
[WHEN warunek THEN wyrazenie]
ELSE wyrazenie
END;
Zobacz: warunki.sql 10
Zobacz: petle.sql
przyklad.sql
PL/pgSQL posiada bogaty zestaw pętli iteracyjnych. Najprostszą jest pętla nieskończona, z której można wyjść tylko za pomocą instrukcji EXIT:
<<infinite>>
[<< etykieta>>]
LOOP n:=n+1;
LOOP instrukcje
EXIT infinite WHEN n>=10;
END LOOP;
END LOOP;
W powyższym przykładzie instrukcja EXIT zakończy pętlę o etykiecie infinite, gdy n>=10. Program przejdzie do wykonania następnej instrukcji po pętli. EXIT może występować tylko w
P e
r w
ócn
z ą
t
p rz
owp
y ę
ż tsli
z .ej, PL/pgSQL udostępnia również pętle WHILE i FOR o sładni:
[<< etykieta>>]
[<< etykieta>>]
WHILE wyra?enie
FOR nazwa IN [REVERSE] od..do
LOOP
LOOP
instrukcje
instrukcje
END LOOP;
END LOOP;
11
Dotychczasowe funkcje używały tylko operacji będących napisami stałymi. Czasami zachodzi potrzeba zdefiniowania funkcji, której sposób wykonania, tj. odwołania do wartości, kolumn, czy funkcji będą wyliczane dynamicznie za każdym razem, gdy funkcja jest wywoływana. Służy do tego instrukcja:
EXECUTE ciag_znakow;
Ciąg znaków, będący wykonywanym zapytaniem, można tworzyć
dynamicznie wewnątrz funkcji, za pomocą operatorów operatorów manipulowania ciągami znaków. Należy zwrócić szczególną uwagę na odpowiednie użycie apostrofów dla nazw i literałów wewnątrz ciągu znaków.
Wszystkie nazwy tabel i kolumn powinny być przetwarzane za
pomocą funkcji quote_ident, natomiast wartości należy przetwarzać za pomocą funkcji quote_literal.
Zobacz: dynamiczne.sql
12
Zobacz: fun_sql.sql
Do tworzenia funkcji można używać także języka SQL. W tym celu trzeba określić język procedury jako 'SQL' i korzystać z instrukcji języka SQL zamiast z instrukcji języka PL/pgSQL.
Instrukcje SQL przyjmują parametry, do kórych można się
odwoływać używając znaków $1, $2, ... . Nie są dostępne instrukcje sterujące, dostępne w PL/pgSQLu. Można jedynie korzystać z
instrukcji SQL. Oznacza to, że o ile PL/pgSQL oferuje deklaracje zmiennych, wartościowania warunkowe i pętle, to SQL oferuje jedynie zastępowanie argumentów wywołania funkcji wartościami wywołania. Wartością zwracaną przez funkcję SQL są dane zwracane przez ostatnio wykonywaną instrukcję SQL, zwykle jest to SELECT.
Zaletą funkcji SQL jest to, że nie musimy ładować do bazy danych obsługi języka PL/pgSQL. Dodatkowo funkcje SQL umożliwiają
zwracanie więcej niż jednego wiersza danych, jeżeli zadeklarujemy typ zwracanej wartości jako setof tabela.
13
Dotychczas poznaliśmy dwa mechanizmy zapewniające sprawdzanie i zachowanie logiki bazy danych. Są nimi klucze obce oraz warunki logiczne nałożone na tabelę w trakcie jej tworzenia. Oba te warunki mają charakter statyczny i nie pozwalają na wiązanie konkretnych akcji ze zdarzeniami zachodzącymi w bazie, a nie z jej stanem.
Takie wiązanie akcji ze zdarzeniami jest możliwe dzięki
procedurom wyzwalanym (ang. triggers). Za pomocą procedury wyzwalanej, w PostgreSQLu, można spowodować, że wykona się
procedura zapamiętana w bazie danych, jeżeli dla określonej tabeli będą podjęte takie działania jak INSERT, UPDATE oraz DELETE.
Składnia polecenia tworzącego procedurę wyzwalaną:
CREATE TRIGGER nazwa {BEFORE | AFTER}
{ zdarzenie [OR ...]}
ON tabela FOR EACH {ROW | STATEMENT}
EXECUTE PROCEDURE funkcja( argumenty); 14
Wyzwalacz zadziała, gdy zajdzie określone zdarzenie (INSERT, DELETE lub UPDATE). Możemy zażądać by zadziałał on po (AFTER) zajściu zdarzenia. Wówczas wywołana procedura będzie miała
dostęp do danych pierwotnych (UPDATE i DELETE) oraz do nowych danych (UPDATE i INSERT). Możemy również zażądać by zadziałał
przed (BEFORE) zajściem zdarzenia. W takim przypadku możemy zabezpieczyć przed aktualizacją lub zmienić dane, które mają być wprowadzane lub aktualizowane.
Dla zdarzeń dotyczących wielu wierszy, spowodowanych pojedynczą instrukcją, możemy wybrać: czy wyzwalacz ma działać dla każdego aktualizowanego wiersza (ROW), czy raczej raz dla całej operacji aktualizacji (STATEMENT).
Kiedy spełnione są określone warunki, wyzwalacz wykonuje specjalny rodzaj procedury przechowywanej w bazie danych, wzawnej
procedurą wyzwalaną. Są one podobne do zwykłych funkcji
przechowywanych w bazie, lecz podlegają pewnym ograniczeniom.
15
Procedury wyzwalane ograniczenia
Procedura wyzwalana jest składowana przez PostgreSQL jako
funkcja bez parametrów oraz o specjalnym typie wyniku OPAQUE.
Typ ten stosuje się dla funkcji zwracających wartości, których PostgreSQL nie może bezpośrednio przetwarzać.
Procedura wywołana przez procedurę wyzwalaną, w reakcji na
zmiany w określonej tabeli, musi zwracać NULL lub wiersz odpowiadający strukturze zmienianej tabeli.
Dla wyzwalaczy typu AFTER, które wykonuje się po operacji UPDATE, zaleca się, aby procedura wyzwalana zwracała wartość NULL.
Dla wyzwalaczy typ BEFORE, zwracany wynik wykorzystuje się do sterowania aktualizacją, która ma być wykonana. Jeżeli procedura wyzwalana zwraca NULL, operacja UPDATE nie jest wykonywana.
Jeżeli zwracany jest wiersz danych, jest on wykorzystany jako źródło aktualizacji, dając procedurze okazję do zmiany danych przed zatwierdzeniem ich w bazie danych.
16
Zobacz: trigger1.sql