PL/pgSQL

PL/pgSQL
	; Logo języka
Pojawienie się	30 października 1998
Twórca	Jan Wieck
	Strona internetowa

PL/pgSQL jest językiem proceduralnym wspieranym przez RDBMS PostgreSQL. Język ten jest bardzo podobny do języka proceduralnego PL/SQL w bazie Oracle.

PL/pgSQL jest prawdziwym językiem programowania, o znacznie większych możliwościach niż język SQL, umożliwiającym m.in. stosowanie pętli i zaawansowanych struktur sterujących. Programy napisane w języku PL/pgSQL są funkcjami, które można stosować jako część polecenia SQL albo jako wyzwalacz.

Główne zalety języka PL/pgSQL:

może być używany do tworzenia funkcji i wyzwalaczy,
dodaje struktury sterujące do języka SQL,
można wykonywać złożone obliczenia,
dziedziczy wszystkie typy zdefiniowane przez użytkownika, funkcje i operatory,
może zostać zdefiniowany jako zaufany dla serwera,
jest łatwy w użyciu.

PL/pgSQL nie jest jedynym "PL" instalowanym domyślnie dla PostgreSQL, lecz dostępnych jest wiele innych, takich jak: PL/Java, PL/Perl, plPHP, PL/Python, PL/R, PL/Ruby, PL/sh, PL/Tcl.

Funkcje w PostgreSQL

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.

Usuwanie funkcji

Funkcje usuwa się za pomocą polecenia DROP FUNCTION:

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

Przykład

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-THEN-ELSE

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

NULLIF

NULLIF(wejście,wartość)

Funkcja ta zwraca wartość NULL, jeżeli wartością wyrażenie wejście=wartość jest TRUE, w innym przypadku zwraca wartość wejście.

CASE

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

Pętle

WHILE

WHILE wyrażenie
	LOOP
		instrukcje
	END LOOP;

FOR

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:

Składnia

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

Innymi słowy:

Stwórz [lub zastąp] wyzwalacz nazwa, przed lub po

operacji {INSERT | UPDATE | DELETE} lub {INSERT | UPDATE | DELETE} lub …

na tabeli, dla każdego wiersza lub raz dla każdego wywołania aktualizacji,

i wykonaj procedurę.

Wewnątrz procedury wyzwalanej dostępne są specjalne zmienne, m.in.

new – rekord zawierający nowy wiersz bazy danych

old – rekord zawierający stary wiersz bazy danych

Zawierają one (dla wyzwalaczy typu ROW) dane z wierszy, których dotyczą te operacje aktualizacji, które spowodowały zadziałanie wyzwalacza. OLD zawiera dane sprzed aktualizacji, natomiast NEW zawiera dane po aktualizacji (lub proponowany wiersz dla wyzwalaczy typu BEFORE).

Usuwanie wyzwalacza

Wyzwalacze usuwa się za pomocą polecenia DROP TRIGGER:

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

Bibliografia

Bazy danych i PostgreSQL od podstaw, R.Stones, N. Matthew, Wydawnictwo Helion 2002

Linki zewnętrzne

dokumentacja PostgreSQL PL/pgSQL