PL/pgSQL


PL/pgSQL w encyklopedii

Z Wikipedii, wolnej encyklopedii Przejdź do nawigacji Przejdź do wyszukiwania

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.

Spis treści

Funkcje w PostgreSQL | edytuj kod

Polecenie CREATE FUNCTION | 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 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 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 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 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 kod

IF-THEN-ELSE | edytuj kod

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

NULLIF | 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 kod

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

Pętle | edytuj kod

WHILE | edytuj kod

WHILE wyrażenie LOOP instrukcje END LOOP; 

FOR | 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 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 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 kod

Wyzwalacze usuwa się za pomocą polecenia DROP TRIGGER:

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

Bibliografia | edytuj kod

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

Linki zewnętrzne | edytuj kod


Na podstawie artykułu: "PL/pgSQL" pochodzącego z Wikipedii
OryginałEdytujHistoria i autorzy