PL/pgSQL

Z Wikipedii, wolnej encyklopedii
PL/pgSQL
Logo języka 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[edytuj | edytuj kod]

Polecenie CREATE FUNCTION[edytuj | edytuj kod]

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[edytuj | edytuj kod]

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[edytuj | edytuj kod]

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[edytuj | edytuj kod]

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[edytuj | edytuj kod]

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[edytuj | edytuj kod]

IF-THEN-ELSE[edytuj | edytuj kod]

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

NULLIF[edytuj | edytuj kod]

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[edytuj | edytuj kod]

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

Pętle[edytuj | edytuj kod]

WHILE[edytuj | edytuj kod]

WHILE wyrażenie
	LOOP
		instrukcje
	END LOOP;

FOR[edytuj | edytuj kod]

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[edytuj | edytuj kod]

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[edytuj | edytuj kod]

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[edytuj | edytuj kod]

Wyzwalacze usuwa się za pomocą polecenia DROP TRIGGER:

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

Bibliografia[edytuj | edytuj kod]

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

Linki zewnętrzne[edytuj | edytuj kod]