PL/pgSQL

Logo języka
Pojawienie się	30 października1998
Twórca	Jan Wieck
Strona internetowa

PL/pgSQL jest językiem proceduralnym wspieranym przezRDBMSPostgreSQL. Język ten jest bardzo podobny do języka proceduralnegoPL/SQL w bazieOracle.

PL/pgSQL jest prawdziwymjęzykiem programowania, o znacznie większych możliwościach niż językSQL, 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 jakowyzwalacz.

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 tworzone są za pomocą polecenia CREATE FUNCTION. Uproszczona składnia wygląda następująco:

CREATEFUNCTIONnazwa([rodzaj_argumentu][nazwa_argumentu]typ_argumentu[,...])RETURNStyp_wynikuAS'     definicja           'LANGUAGE'nazwa_języka'

Przykład

CREATEFUNCTIONsubt_one(int4)RETURNSINT4AS'BEGINRETURN $1 -1;END;'LANGUAGE'plpgsql';

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

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-Upostgresplpgsqlbpfinal-L/usr/local/pgsql/lib

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

DROPLANGUAGE'plpgsql';

W języku PL/pgSQL nie ma znaczenia wielkość liter wsł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

SELECTnazwa_funkcji(argumenty)

lub

SELECT*FROMnazwa_funkcji(argumenty)

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

CREATE[ORREPLACE]FUNCTIONnazwa([rodzaj_argumentu][nazwa_argumentu]typ_argumentu[{DEFAULT|=}wartość_domyślna}][,...])[RETURNStyp_wyniku|RETURNSTABLE(nazwa_kolumnytyp_kolumny[,...])]AS‘DECLARE--deklaracje zmiennych, skladnia:nazwa[CONSTANT]typ[NOTNULL][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 NULLnp.: */n1integer;BEGINinstrukcje--komentarz jednowierszowy/* komentarzwielowierszowy – zagnieżdżanie takich nie jest dozwolone.Po BEGIN można tworzyć nowe bloki, (zmienne mają zasięg w zadeklarowanym bloku):DECLAREzmienneBEGINinstrukcjeEND;*/RETURN[wartość]--obowiązkowo funkcja musi zwracać wartośćEND;‘LANGUAGE'plpgsql';

Typ zmiennej w sekcji DECLARE może być jednym zwbudowanych typów PostgreSQL,użytkownika lubodpowiadającym wierszowi w tabeli.

Funkcje usuwa się za pomocą polecenia DROP FUNCTION:

DROPFUNCTION[IFEXISTS]nazwa_funkcji[([typ_argumentu[,…]])],[,...][CASCADE|RESTRICT]

Przykład

DROPFUNCTIONsubt_one(int4);

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:

nazwaALIASFOR$n

IFwyrażenieTHENinstrukcje–zagnieżdżaniedozwolone[ELSEinstrukcje–zagnieżdżanierównieżdozwolone]ENDIF;

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.

CASEWHENwyrażenieTHENwyrażenieELSEwyrażenieEND;

WHILEwyrażenieLOOPinstrukcjeENDLOOP;

FORnazwaIN[REVERSE]od..doLOOPinstrukcjeENDLOOP;

FORwierszINSELECT[treśćzapytania]LOOPinstrukcjeENDLOOP;--pętla wykonuje się dla każdego wiersza zwróconego przez SELECT

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[ORREPLACE]TRIGGERnazwa{BEFORE|AFTER}{INSERT|UPDATE|DELETE[OR...]}ONtabelaFOREACH{ROW|STATEMENT}EXECUTEPROCEDUREfunkcja(argumenty)

Innymi słowy:

Stwórz [lub zastąp] wyzwalacznazwa, 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).

Wyzwalacze usuwa się za pomocą polecenia DROP TRIGGER:

DROPTRIGGER[IFEXISTS]nazwa_triggeraONnazwa_tabeli[CASCADE|RESTRICT];

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

• dokumentacja PostgreSQL PL/pgSQL

Zobacz publikację Procedury składowane w PostgreSQL w Wikibooks

• SQL
• PostgreSQL