PL/pgSQL (Procedural Language/postgresSQL) rozszerza SQL o typowe konstrukcje spotykane w proceduralnych jezykach imperatywnych. Podstawową jednostką w PL/SQL jest blok, programy buduje się z zagnieżdżonych bloków.
W blokach języka PL/pgSQL wolno używać instukcji SQL służących do
manipulowania danymi (np. SELECT, INSERT, UPDATE,
DELETE) oraz instrukcji sterowania transakcjami. Dla instrukcji
SELECT jest używana postać rozszerzona, pozwalająca umieszczać
wyszukane wartości w zmiennych PL/pgSQL. Instrukcje definiowania danych,
takie jak CREATE, DROP czy ALTER, nie są dozwolone.
Poza tym w blokach można używać typowych instrukcji takich jak przypisanie, instrukcja warunkowa, pętle, wywołania procedur.
Bloki PL/pgSQL umieszcza się w treści funkcji, które można uruchamiać zwykłą
instrukcją SELECT z poziomu programu psql.
Funkcje definiuje się w Postgresie używając konstrukcji
CREATE FUNCTION nazwa(typ-parametru, ...) RETURNS typ-wyniku AS $$ treść-funkcji $$ LANGUAGE nazwa-języka;
W definicji nie określa się nazw parametrów, odwołania do parametrów w treści
funkcji mają postać $1, $2 itd.
Treść funkcji w starszych wersjach Postgresa otaczało się apostrofami, było to jednak niewygodne, bo wymagało dublowania wszystkich apostrofów wewnątrz treści.
Ponieważ w Postgresie polecenia SELECT można używać do obliczania
wyrażeń nie związanych z bazą danych, np.
bd> SELECT 2 + 5 AS siedem; siedem ------ 7
więc najprostsze funkcje można definiować w SQL:
CREATE FUNCTION dodaj7(int4) RETURNS int4 AS $$ SELECT $1 + 7; $$ LANGUAGE sql;
Przykład wywołania takiej funkcji:
bd> SELECT dodaj7(4) AS wynik; wynik ----- 11
Treść funkcji w SQL to ciąg poleceń SQL.
Typami parametrów mogą być nazwy tabel:
CREATE FUNCTION superwaga(Zwierz) RETURNS integer AS $$ SELECT $1.waga * 2 AS waga; $$ LANGUAGE sql;
i można się do takich funkcji odwoływać następująco:
bd> SELECT imie, superwaga(zwierz) FROM zwierz;
imie | superwaga
--------------+------------
Misza | 248
Lonia | 270
Toni | 108
Leszek | 278
Ania | 270
Zora | 110
Muszka | 66
...
Jeśli argument jest typu złożonego można używać w odwołaniach do jego
atrybutów notacji ,,kropkowej'', np. $1.emp.
Funkcje napisane w SQL zwracają jako wartość wynik ostatniego polecenia w treści, musi to być SELECT. Jeśli wynik nie ma być zbiorem, będzie to pierwszy wiersz z tego zapytania (warto więc zadbać o ewentualne ORDER BY ;-). Jeśli wynik zapytania był pusty, to jest zwracane NULL.
Jeśłi funkcja SQL ma zwracać zbiór, to typem wyniku musi być SETOF typ. Zwracane są wtedy wszystkie wiersze z ostatniego zapytania w treści.
Dla funkcji SQL, które nie zwraca nic rozsądnego typem wyniku powinno być void, nie może ona wtedy kończyć się zapytaniem SELECT.
CREATE FUNCTION oczyść () RETURNS void AS $$ DELETE FROM Zwierz WHERE waga <= 0; $$ LANGUAGE SQL; SELECT oczyśc();
Funkcje tabelowe są to funkcje zwracające zbiory wierszy, ich wywołania umieszcza się w klauzulach FROM zapytań.
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
SELECT * FROM foo
WHERE foosubid in (select foosubid from getfoo(foo.fooid) z
where z.fooid = foo.fooid);
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
Normalnie jednak procedury bazy danych definiuje się nie w SQL, lecz w językach proceduralnych. Takim językiem dla serwera bazy danych w Postgresie jest najczęściej PL/pgSQL.
Domyślnie na serwerze instalowane są tylko języki C i SQL. Inne (w tym PL/pgSQL) trzeba instalować samemu. Odbywa się to w 3 krokach:
Moduł obsługi języka (,,handler'') musi mieć postać biblioteki dzielonej, najlepiej umieszczonej w katalogu bibliotecznym Postgresa. Dla PL/pgSQL jest to zwykle
/usr/local/pgsql/lib/plpgsql.so
|
Moduł obsługi należy zadeklarować poleceniem
CREATE FUNCTION nazwa-modułu-obsługi () RETURNS LANGUAGE_HANDLER AS 'pełna-nazwa-biblioteki' LANGUAGE 'C';
Specjalny typ wyniku LANGUAGE_HANDLER przekazuje bazie danych
informację, że funkcja nie zwraca żadnego ze zdefiniowanych w SQL typów
i nie można jej bezpośrednio użyć w poleceniach SQL.
Uwaga: dla zgodności ze starszymi wersjami zamiast
LANGUAGE_HANDLER można użyć OPAQUE.
Dla PL/pgSQL może to wyglądać następująco
CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
Język programowania deklaruje się poleceniem
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE nazwa HANDLER nazwa-modułu-obsługi;
Polecenie to nie występuje w SQL-92.
Opcjonalne słowo TRUSTED oznacza, że zwykli użytkownicy mogą
definiować własne funkcje i procedury wyzwalaczy.
Nazwa może być podana bezpośrednio lub jako napis (tzn. w apostrofach).
PL/pgSQL deklarujemy więc następujaco
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
Dla standardowych języków dystrybucji istnieje skrypt createlang, wykonujący te same czynności. Aby zainstalować PL/pgSQL w bazie danych template1 wystarczy użyć
$ createlang plpgsql template1
Podczas tworzenia nowej bazy danych dziedziczy ona zainstalowane języki z
wzorcowej bazy (domyślnie jest nią template1). Dlatego
administratorzy często instalują potrzebne języki we wzorcowej bazie danej
instalacji. Informacje o zainstalowanych językach proceduralnych znajdują
się w katalogowej tabeli pg_language
Do usuwania języka proceduralnego służy polecenia DROP LANGUAGE albo skrypt droplang.
Blok jest podstawową konstrukcją języka PL/pgSQL. Składnia bloku:
[DECLARE deklaracje zmiennych, stałych i procedur lokalnych>] BEGIN instrukcje END;
(nawiasy kwadratowe oznaczają część opcjonalną, nie są elementem składni.)
Przykład:
DECLARE
a NUMERIC(5);
BEGIN
SELECT COUNT(*) INTO a
FROM EMP
WHERE ENAME LIKE 'A%';
IF a > 0 THEN
INSERT INTO TEMP VALUES(a);
END IF;
RAISE NOTICE 'OK';
END;
Oprócz komentarzy SQL (--) można używać komentarzy blokowych
w stylu języka C (/* ... */).
Transmisja informacji między programami w PL/pgSQL i bazami danych odbywa się za pośrednictwem zmienych. Z każdą zmienną jest związany jej typ. Typ zmiennej może być:
jednym z typów uzywanych w SQL dla kolumn tabel;
typem generycznym takim jak NUMERIC;
zadeklarowany jako typ określonej kolumny w bazie danych.
Najczęściej używanym typem generycznym jest NUMERIC. W zmiennych
typu NUMERIC można przechowywać liczby całkowite lub rzeczywiste.
Dla napisów najczęściej używa się typu VARCHAR(n), gdzie n
jest maksymalna długością napisu. Przykład:
DECLARE cena NUMERIC; piwo VARCHAR(20);
Ponieważ zmienne PL/pgSQL często służa do operowania na danych
pochodzących z kolumn tabel, muszą one w takim przypadku być z nimi
dokładnie zgodne co do typu. Najłatwiej osiągnąć to używając w ich
deklaracjach operatora %TYPE, na przykład:
DECLARE piwo Piwa.nazwa%TYPE;
nadaje zmiennej piwo taki typ, jaki został zadeklarowany dla kolumny nazwa podczas tworzenia tabeli Piwa.
Typ rekordowy jest typem złożonym z kilku nazwanych pól. Dla zmiennych tego
typu można nie ustalać nazw pól używając deklaratora RECORD. Robi
się tak czasem w procedurach wyzwalaczy, gdy mogą one być wołane dla
różnych tabel. Częściej jednak typ zmiennej rekordowej określa się
jako składający się z pól odpowiadających kolumnom określonej tabeli.
Podaje się wtedy deklarator %ROWTYPE po nazwie tabeli, na przykład
DECLARE wiersz RECORD; opis_piwa Piwa%ROWTYPE;
jeśli tabela Piwa ma schemat Piwa(nazwa, producent, bar),
to zmienna opis_piwa będzie składać się z pól opis_piwa.nazwa,
opis_piwa.producent oraz opis_piwa.bar.
Niezależnie od typu początkową wartością każdej zmiennej jest NULL. Można jednak nadać im inną wartość początkową już w deklaracji, używając operatora przypisania :=. Oczywiście w treści funkcji mogą także wystąpić instrukcje przypisania, na przykład:
DECLARE a NUMERIC := 3; BEGIN a := a + 1; END;
Instrukcja warunkowa:
IF warunek THEN instrukcje1 [ELSE instrukcje2] END IF;
Przykład:
IF b <> 0 THEN a := 1 / b; ELSE a := 0; END IF;
Bardziej rozbudowany przykład (z warunkowymi wstawieniami do tabeli):
DECLARE
a NUMERIC;
b NUMERIC;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
IF b=1 THEN
INSERT INTO T1 VALUES(b,a);
ELSE
INSERT INTO T1 VALUES(b+10,a+10);
END IF;
END;
Pętle tworzy się konstrukcją LOOP:
[etykieta-pętli] LOOP ... EXIT [etykieta-pętli] [WHEN warunek] ... END LOOP [etykieta-pętli];
Instrukcja EXIT służy do wychodzenia z pętli (ewentualnie tylko gdy warunek jest spełniony). Połączona z etykietą pozwala wychodzić z pętli zagnieżdżonych.
Przykład:
<<zewn>>
LOOP
LOOP
EXIT; -- wychodzi z petli wewnętrznej
EXIT zewn; -- wychodzi z obu pętli
END LOOP;
END LOOP zewn;
Przykład na wstawienie par od (1, 1) do (100, 100) do tabeli T1:
DECLARE
i NUMERIC := 1;
BEGIN
LOOP
INSERT INTO T1 VALUES(i,i);
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;
Inne kostrukcje sterujące uzyskuje się odpowiednio poprzedzając instrukcję LOOP.
Pętla WHILE ma skłdnię:
[etykieta-pętli] WHILE warunek LOOP instrukcja ... END LOOP;
zaś pętla FOR:
FOR zmienna IN [REVERSE] wyrażenie..wyrażenie LOOP instrukcja ... END LOOP;
Przykład:
k:=5; FOR i IN 1..k LOOP RAISE NOTICE '#'; END LOOP;
Zmienna sterująca jest lokalna w pętli i nie musi być deklarowana, ale wtedy jest dostępna tylko wewnątrz pętli.
Instrukcje DML (SELECT, UPDATE, DELETE, INSERT) można umieszczać bezpośrednio w kodzie SQL.
Instrukcja SELECT w programach musi zawierać frazę INTO, w przeciwnym razie (gdy wykonujemy ją tylko dla efektu ubocznego, np. wołając jakąś funkcję) należy początkowy znacznik SELECT zastąpić przez PERFORM.
Gdy mamy pewność, że instrukcja SELECT zwróci pojedynczą krotkę, można wyszukane atrybuty tej krotki umieścić w zmiennych, wg składni:
SELECT wyrażenie1,wyrażenie2,... INTO zmienna1,zmienna2,... FROM ... WHERE ... itd.
np.
DECLARE
b number(3);
c number(3);
BEGIN
SELECT COUNT(*),COUNT(a) INTO b,c FROM TEMP;
IF (b > c) THEN
RAISE NOTICE 'W kolumnie a występują wartości NULL';
END IF;
END;
Do sprawdzania, czy zapytanie zwróciło jakiś wiersz, służy specjalna zmienna FOUND:
numer := 113; SELECT * INTO prac FROM Pracownicy WHERE nrprac = numer; IF NOT FOUND THEN RAISE EXCEPTION 'Nie ma pracownika numer %.',numer; END IF;
Aby móc sensownie korzystać z zapytań zwracających bardziej (lub mniej) liczne zbiory krotek, należy użyć kursorów. Kursor jest ,,zmienną'' przebiegającą po krotkach relacji. Relacja może być tabelą, zwykle jednak jest wynikiem zapytania. Program może przetwarzać wynik zapytania, pobierając z kursora kolejne krotki relacji. Dla tabel można ponadto modyfikować lub usuwać bieżącą krotkę kursora.
Kursor można zwrócić jako wartość funkcji, a następnie użyć go w procedurze wywołującej do przeglądania wierszy. Jest to efektywna metoda zwracania dużych zbiorów wierszy z funkcji.
Dostęp do kursorów w PL/pgSQL odbywa się przez zmienne typu refcursor. Zmienne takie można deklarować bezpośrednio lub używając deklaracji kursora o składni:
nazwa CURSOR [(argumenty)] FOR zapytanie;
(zamiast FOR można użyć IS dla zgodności z Oracle.) Opcjonalne argumenty to rozdzielona przecinkami lista par
| nazwa typ |
Przykłady deklaracji kursorów:
DECLARE kurs1 refcursor; kurs2 CURSOR FOR SELECT * FROM Gatunki; kurs3 CURSOR (w integer) IS SELECT * FROM Zwierz WHERE wiek = w;
Wszystkie powyższe zmienne są typu refcursor. Pierwszej można użyć z dowolnym zapytaniem, drugiej tylko z zapytaniem związanym z nią, zaś ostatnia związana jest z parametryzownym zapytaniem (parametr w podczas otwierania kursora zostanie zastąpiony liczbą całkowitą).
Aby móc pobierać wiersze z kursora należy go otworzyć. Jeśli podczas deklarowania kursor nie został związany z żadnym zapytaniem, należy użyć OPEN FOR SELECT:
OPEN niezwiązany-kursor FOR SELECT ...;
Otwarcie kursora oznacza przygotowanie zapytania do wykonania (wraz z ewentualnym podstawieniem dla zmiennych PL/pgSQL), np.
OPEN kurs1 FOR SELECT * FROM Zwierz WHERE wiek = w;
Można także użyć OPEN FOR EXECUTE
OPEN niezwiązany-kursor FOR EXECUTE napis;
aby móc dynamicznie konstruować zapytanie w postaci napisu, np.
OPEN kurs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
Związany kursor otwiera się znacznie prościej
OPEN związany-kursor [(argument,...)];
Argumenty podaje się tylko wtedy, jeśli podczas deklaracji użyto parametryzowanego zapytania. Przykłady:
OPEN kurs2; OPEN kurs3(4);
Po otwarciu kusora można go używać do pobierania wierszy z wyniku zapytania. Można go również zwrócić jako wartość funkcji, co ma skutek podobny do zwrócenia ,,tabeli''. Trzeba jednak pamiętac, że kursory są niejawnie zamykane na koniec transakcji.
Instrukcją FETCH pobieramy następny wiersz z kursora
FETCH kursor INTO bufor;
Buforem może być zmienna wierszowa (%ROWTYPE), zmienna rekordowa lub
rozdzielona przecinkami odpowiednia lista zmiennych prostych, podobnie jak
dla SELECT INTO. Zmiennej FOUND można użyć do sprawdzenia,
czy coś dostaliśmy:
FETCH kurs1 INTO zwierzak; FETCH kurs2 INTO gat, kont, ochr, zjada;
Kursor zamykamy instrukcją CLOSE
CLOSE cursor;
na przykład aby go zwolnić przed końcem transakcji
CLOSE kurs1;
Poniższy przykład wypisuje imiona dwóch najcięższych zwierzaków o wadze nie przekraczającej 100 kg.
DECLARE
CURSOR zwierzaki_wg_wagi(do_wagi number) FOR
SELECT imie,waga
FROM Zwierz
WHERE waga <= do_wagi
ORDER BY waga DESC;
imie Zwierz.imie%TYPE;
waga Zwierz.waga%TYPE;
zwierzak zwierzaki_wg_wagi%ROWTYPE;
BEGIN
OPEN zwierzaki_wg_wagi(100);
/* FETCH pobiera kolejny rekord z otwartego kursora.
W tym przypadku kolejne atrybuty pobranej krotki umieszczane
sa w kolejnych zmiennych */
FETCH zwierzaki_wg_wagi into imie,waga;
/* Zmienna FOUND ma wartosc TRUE wtw, gdy ostatni FETCH zwrocil rekord */
IF FOUND THEN
RAISE NOTICE imie;
/* Tu cala krotka jest umieszczana w zmiennej 'zwierzak' */
FETCH zwierzaki_wg_wagi into zwierzak;
IF FOUND THEN
RAISE NOTICE zwierzak.imie;
END IF;
END IF;
CLOSE zwierzaki_wg_wagi; -- po uzyciu kursor trzeba zamknac
END;
A oto inny przykład. Z tabeli T1(e,f), zawierającej pary liczb całkowitych, usuwamy wszystkie krotki z pierwszym elementem mniejszym od drugiego, zamiast nich wstawiając krotki z odwróconymi elementami.
DECLARE
/* Zmienne na wynik zapytania: */
a T1.e%TYPE;
b T1.f%TYPE;
CURSOR T1Cursor FOR
SELECT e, f
FROM T1
WHERE e < f
FOR UPDATE;
BEGIN
OPEN T1Cursor;
LOOP
-- Pobieranie kolejnego wiersza z kursora
FETCH T1Cursor INTO a, b;
-- Kończymy gdy brak wierszy
EXIT WHEN NOT FOUND;
IF a < b THEN
-- Usuwamy bieżącą krotkę
DELETE FROM T1 WHERE CURRENT OF T1Cursor;
-- Wstawiamy odwrócona krotkę
INSERT INTO T1 VALUES(b, a);
END IF;
END LOOP;
-- Zwalniamy kursor
CLOSE T1Cursor;
END;
Funkcje PL/pgSQL mogą jako swoja wartość zwracać otwarty kursor. Funkcja wywołująca może wtedy pobierać wiersze z tego kursora
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc2() RETURNS refcursor AS $$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
$$ LANGUAGE plpgsql;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
Zamiast bezpośrednio używać kursorów można skorzystać ze
specjalnej postaci pętli FOR.
Składnia:
DECLARE
<zmienna rekordowa> RECORD;
...
BEGIN
...
FOR <zmienna rekordowa> IN SELECT ... LOOP
...
END LOOP;
Instrukcje w pętli wykonują się po kolei dla każdego rekordu zwracanego przez kursor. Pętla otwiera i zamyka kursor automatycznie, nawet przy skoku na zewnątrz pętli (exit,goto) lub zgłoszeniu wyjątku.
W poniższym przykładzie wypisujemy wszystkie wagi zwierzaków nie większe niż 200 kg.
FOR p IN SELECT imie,waga
FROM Zwierz
WHERE waga <= 200
ORDER BY waga DESC
LOOP
RAISE NOTICE '%', p.waga;
END LOOP;
Funkcje tworzymy poleceniem CREATE FUNCTION. Składnia:
CREATE [OR REPLACE] FUNCTION <nazwa funkcji> ([<lista parametrów>])
RETURNS <typ wyniku> AS $$
DECLARE
...
BEGIN
...
END;
$$ LANGUAGE 'plpgsql';
Parametry deklaruje się podając ich typy. MOżna również podać ich nazwy. Jeśłi tego nie zrobimy, to w treści odwołujemy się do nich przez $1, $2 ..., najlepiej nazywając je deklaracją ALIAS w sekcji DECLARE
DECLARE subtotal ALIAS FOR $1; ...
Przykład (tworzy funkcję samodzielną, przechowywaną w bazie danych):
-- funkcja liczy sume zarobkow w zadanym dziale CREATE OR REPLACE FUNCTION suma_zarobkow(dzial varchar2) RETURNS number AS $$ DECLARE suma number(6); BEGIN SELECT sum(sal) INTO suma FROM EMP WHERE deptno = (select deptno from dept where dname = dzial); RETURN suma; END; $$ LANGUAGE 'plpgsql';
Tak zdefiniowanej funkcji można używać np. w zapytaniach.
Klauzula OR REPLACE powoduje, że istniejąca specyfikacja zostanie zastąpiona nową.
Funkcje usuwa się instrukcją:
DROP FUNCTION nazwa-funkcji (lista-typów-parametrów);
Podanie listy typów jest konieczne, ponieważ można deklarować funkcje polimorficzne.
RETURN wyrażenie;
RETURN z wyrażeniem służy do zwracania wartościz funkcji prostej (tzn. takiej, która nie zwraca zbioru). Wywołanie funkcji kończy się i wartość wyrażenia jest zwracana do kontekstu wywołującego. Wartość wyrażenia zostanie automatycznie dostosowana do typu funkcji (o ile to możliwe). Jeśli typem funkcji jest void, to można pominąć wyrażenie, zresztą jego wartość i tak będzie zignorowana.
RETURN NEXT wyrażenie;
Jeśli typem funkcji jest SETOF sometype, wartości zwracane określa się instrukcjami RETURN NEXT. Nie powodują one zakończenia obliczania funkcji, lecz jedynie ,,budują'' wynik. Dopiero instrukcja RETURN bez argumentów powoduje zakończenie funkcji. Funkcje tego typu należy traktować jako zwracające ,,tabele'' i umieszczać ich wywołania we frazie FROM:
SELECT * FROM spora_funkcja();
Błędy sygnalizujemy używając instrukcji
RAISE EXCEPTION '...';
Powoduje ona wypisanie komunikatu i wycofanie bieżącej transakcji.
Wyzwalacze są specjalną konstrukcją PL/SQL, będącą rozszerzeniem procedur. Podczas gdy procedura musi być wywołana jawnie (z innej procedury lub instrukcji SELECT), wyzwalacze wywoływane są automatycznie zawsze wtedy, gdy wystąpią związane z nimi zdarzenia. Zdarzeniem jest modyfikacja tabeli.
Wyzwalacze w PL/pgSQL definiuje się używając uprzednio zdefiniowanych procedur -- bezargumentowych funkcji zwracajacych specjalny typ TRIGGER (funkcja wyzwalacza otrzymuje dane za pośrednictwem struktury TriggerData, a nie przez zwykłe parametry funkcyjne). Procedur tych nie należy wywoływać bezpośrednio. Sa one wywoływane niejawnie przez wyzwalacz, ilekroć wystąpi zdarzenie z nim związane. Zdarzeniem może być próba wykonania jednej z operacji SQL INSERT, DELETE lub UPDATE. Dla wyzwalaczy określa się, czy ich procedury mają być wołane przed czy po właściwej operacji (BEFORE lub AFTER). Ponadto dla wyzwalacza określony jest jeden z dwóch poziomów: wiersza lub zdania. Wyzwalacz poziomu zdania jest odpalany tylko raz dla całego polecenia SQL, natomiast wyzwalacz poziomu wiersza jest odpalany niezależnie dla każdego modyfikowanego wiersza.
Argumenty wywołania w definicji wyzwalacza powinny być literałami. Dzięki nim mozna tej samej funkcji używać w różnych wyzwalaczach. Najprostszy przykład to funkcja wpisująca informacje do dodatkowej tabeli zawierającej dziennik modyfikacji, gdzie parametrem mogłaby być nazwa modyfikowanej tabeli.
Funkcja wyzwalacza musi zostać zdefiniowana przed utworzeniem wyzwalacza.
Deklaracja wyzwalacza w PL/pgSQL ma następującą składnię:
CREATE TRIGGER nazwa BEFORE | AFTER INSERT | DELETE | UPDATE [OR ...] ON tabela [FOR EACH ROW | STATEMENT] EXECUTE PROCEDURE nazwa-procedury(argumenty);
Dodatkowe uwagi:
Nazwa wyzwalacza musi być unikalna dla danej tabeli i przydaje się przy usuwaniu wyzwalacza.
Modyfikatory BEFORE i AFTER określają, czy funkcja wyzwalacza ma być wywoływana przed czy po właściwej akcji.
Można określić do trzech rodzajów zdarzeń (INSERT, DELETE lub UPDATE) uruchamiających wyzwalacz używając spójnika OR. Przykłady:
... INSERT ON R ... ... INSERT OR DELETE OR UPDATE ON R ...
Opcja FOR EACH ROW określa, że wyzwalacz jest poziomu wiersza, tzn. będzie odpalany osobno dla każdego zmienianego wiersza tabeli. Domyślnie wyzwalacz jest poziomu całej instrukcji.
Usuwając wyzwalacz trzeba oprócz jego nazwy podać nazwę tabeli
DROP TRIGGER nazwa ON tabela;
W treści funkcji związanej z wyzwalaczem są dostępne dwie zmienne rekordowe NEW i OLD, odnoszące się do nowej i starej zawartości wiersza. Procedura wyzwalacza dla wstawiania i modyfikacji powinna zwracać odpowiedni rekord, zwykle jest to po prostu NEW. Można też zwracać NULL, co w wyzwalaczach typu BEFORE oznacza, że wykonanie wyzwalacza nie powiodło się i operacja powinna zostać zignorowana.
Wyzwalacze tego samego typu uruchamia się w kolejności alfabetycznej według ich nazw. Jeśli w funkcji wyzwalacza używa się poleceń SQL, mogą one spowodować uruchomienie innych wyzwalaczy, nazywa się to kaskadowaniem wyzwalaczy. Nie istnieje żadne ograniczenie na liczbę poziomów wywołań kaskadowych, w szczególności możliwe są wywołania rekurencyjne. Programista jest odpowiedzialny za unikanie nieskończonej rekursji.
Pora na przykłady. Załóżmy, że mamy tabelę
CREATE TABLE Gatunki ( nazwa VARCHAR(30) PRIMARY KEY, kontynent VARCHAR(11), chroniony BOOLEAN, przysmak VARCHAR(15) );
Utworzymy wyzwalacz dbający, aby nazwa kontynentu rozpoczynała się dużą literą:
CREATE FUNCTION normkont () RETURNS TRIGGER AS $$
BEGIN
IF NEW.kontynent IS NOT NULL THEN
NEW.kontynent := lower(NEW.kontynent);
NEW.kontynent := initcap(NEW.kontynent);
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER gatwyzw1
BEFORE INSERT OR UPDATE ON Gatunki
FOR EACH ROW
EXECUTE PROCEDURE normkont();
Zwróćmy uwagę, że wykonanie polecenia CREATE TRIGGER jedynie tworzy wyzwalacz nie wykonując go. Aby uruchomić wyzwalacz należy spowodować zajście odpowiedniego zdarzenia, w tym przypadku wstawienia lub modyfikacji wiersza dla tabeli Gatunki.
Dokładnego omówienia typów w PL/pgSQL.
Różnych szczegółów dotyczących omawianych tematów, które można znaleźć w dokumentacji.
Omówienia dynamicznego SQL-a (czyli jak wykonać zapytanie, którego treść nie jest znana w trakcie kompilacji).
Omówienia środowisk, w których działa PL/pgSQL (serwer, wstawki w C i innych językach).
W dokumentacji on-line: www.postgresql.org/docs.
Jeśli kolumna zadeklarowana jest jako SERIAL, to wyglada na to, że podczas wstawiania otrzymuje ona wartość jeszcze przed odpalaniem ewentualnych wyzwalaczy typu BEFORE.
Pisząc procedury bazy danych trzeba pamiętać, że będą one wykonywane na serwerze, w środowisku innym niż środowisko klienta bazy danych. Pewne pliki mogą nie istnieć lub też być dostępne pod innymi nazwami.