Polecenie CREATE FUNCTION

Funkcje w PostgreSQL tworzone są za pomocą polecenia CREATE FUNCTION. Uproszczona składnia wygląda następująco:

CREATE FUNCTION nazwa ([rodzaj_argumentu] [nazwa_argumentu] typ_argumentu [,...])
     	RETURNS typ_wyniku
     	AS '
     		definicja
           '
     LANGUAGE 'nazwa_języka'

Przykład

CREATE FUNCTION subt_one(int4) RETURNS INT4 AS '
	BEGIN
		RETURN $1 -1;
	END;
' LANGUAGE 'plpgsql';

W tym przypadku wykorzystano język PL/pgSQL. Jest to język specyficzny dla PostgreSQL, w innych bazach danych są dostępne podobne języki. W Oracle na przykład jest to PL/SQL, w Sybase Transact-SQL.

Dodanie języka PL/pgSQL do bazy danych

Aby skorzystać z języka PL/pgSQL, należy samodzielnie zainstalować program obsługi. Ponieważ jest to dość skomplikowana operacja, w PostgreSQL umieszczono skrypt pomocniczy, składnia jest następująca:

createlang [opcje] [nazwa_jezyka] nazwa_bazy_danych

Zwykły użytkownik nie ma prawa dodawać obsługi języka do bazy danych, dlatego zazwyczaj należy połączyć się jako supeużytkownik postgres, wtedy dodajemy przełącznik U z nazwą superużytkownika:

createlang -U postgres plpgsql bpfinal -L/usr/local/pgsql/lib

Usunięcie języka możliwe jest również tylko dla superużytkownika przez wpisanie w konsoli psql polecenia

DROP LANGUAGE 'plpgsql';

W języku PL/pgSQL nie ma znaczenia wielkość liter w słowach kluczowych takich jak BEGIN oraz układ kodu. Możliwe jest również przeciążanie funkcji. Aby użyć znaku apostrofu w definicji funkcji należy poprzedzić go dodatkowym apostrofem.

CREATE FUNCTION jest tylko zapisem kodu funkcji, aby ją skompilować musi zostać wywołana, np. poprzez

SELECT nazwa_funkcji(argumenty)

lub

SELECT * FROM nazwa_funkcji(argumenty)

Pełniejsza składnia polecenia CREATE FUNCTION wygląda następująco:

Składnia CREATE FUNCTION

CREATE [OR REPLACE] FUNCTION nazwa ([rodzaj_argumentu] [nazwa_argumentu] typ_argumentu [{ DEFAULT | = } wartość_domyślna}] [,...])
[RETURNS typ_wyniku | RETURNS TABLE ( nazwa_kolumny typ_kolumny [, ...] ) ]
AS ‘
DECLARE
	--deklaracje zmiennych, skladnia:
	nazwa [CONSTANT] typ [NOT NULL] [DEFAULT | := wartość];
	/* CONSTANT sprawia, że wartości zmiennej nie można zmieniać
	NOT NULL sprawia, że wartości zmiennej nie można przypisać wartości NULL
	np.: */ 
	n1 integer;
BEGIN
	instrukcje --komentarz jednowierszowy
	/* komentarz
		wielowierszowy – zagnieżdżanie takich nie jest dozwolone.
	Po BEGIN można tworzyć nowe bloki, (zmienne mają zasięg w zadeklarowanym bloku):
	DECLARE
		zmienne
	BEGIN
		instrukcje
	END;*/
	RETURN [wartość] --obowiązkowo funkcja musi zwracać wartość
END;
‘ LANGUAGE 'plpgsql';

Typ zmiennej w sekcji DECLARE może być jednym z wbudowanych typów PostgreSQL, użytkownika lub odpowiadającym wierszowi w tabeli.

DROP FUNCTION [ IF EXISTS ] nazwa_funkcji [ ([typ_argumentu [, …]]) ], [, ...] [ CASCADE | RESTRICT ]

DROP FUNCTION subt_one(int4);

Aliasy zmiennych

Odwołania do parametrów, z którymi uruchomiono funkcję, realizuje się poprzez $1, $2 itd. Za pomocą deklaracji ALIAS można się do nich odwoływać za pomocą własnych zdefiniowanych nazw:

nazwa ALIAS FOR $n

Instrukcje warunkowe

IF wyrażenie
	THEN
	instrukcje –zagnieżdżanie dozwolone
	[ELSE
		instrukcje –zagnieżdżanie również dozwolone]
	END IF;

NULLIF(wejście,wartość)

CASE
		WHEN wyrażenie
		THEN wyrażenie
		ELSE wyrażenie
	END;

Pętle

WHILE wyrażenie
	LOOP
		instrukcje
	END LOOP;

FOR nazwa IN [REVERSE] od..do
	LOOP
		instrukcje
	END LOOP;

FOR wiersz IN SELECT [treść zapytania]
	LOOP
		instrukcje
	END LOOP; --pętla wykonuje się dla każdego wiersza zwróconego przez SELECT

Procedury wyzwalane

Za pomocą procedury wyzwalanej (tzw. wyzwalacz, triger) można sprawić, że PostgreSQL wykona automatycznie procedurę zapisywaną w bazie danych, jeżeli dla określonej tabeli będą podjęte takie operacje jak INSERT, UPDATE oraz DELETE.

Aby wykorzystać procedurę wyzwalaną, najpierw należy zdefiniować procedurę, a następnie utworzyć sam wyzwalacz, który określa, kiedy procedura wyzwalana będzie wykonywana.

Tworzy się je za pomocą polecenia CREATE TRIGGER. Składnia:

CREATE [OR REPLACE] TRIGGER nazwa {BEFORE | AFTER}
	{INSERT | UPDATE | DELETE [OR...]}
	ON tabela FOR EACH {ROW | STATEMENT}
	EXECUTE PROCEDURE funkcja(argumenty)

DROP TRIGGER [ IF EXISTS ] nazwa_triggera ON nazwa_tabeli [ CASCADE | RESTRICT ];