Operatory, funkcje
i PL/pgSQL
Tomasz Borzyszkowski
Wstęp
2
Wielokrotnie już korzystaliśmy z operatorów w klauzulach
WHERE
,
czy
HAVING
instrukcji
SELECT
. Np. aby ograniczyć wybór wierszy z
tabeli towarów, do wierszy, które spełniają warunek: cena zakupu
towaru jest większa niż 4, można zastosować operator
>
w
następujący sposób:
SELECT * FROM item WHERE
cost_price > 4
;
Operatory można również składać ze sobą uzyskując bardziej
złożone warunki. Np. towary, których cena sprzedaży kończy się na
99/100, można uzyskać następująco:
SELECT * FROM item WHERE
(sell_price*100)%100 = 99
;
PostgreSQL pozwala także wykonywać skomplikowane operacje na
napisach. Przykładem może być zapytanie:
SELECT * FROM item WHERE
description ~* '^[PR].*E$
;
Wybierające towary, których opisy rozpoczynają się na
P
,
p
,
R
lub
r
i kończą się na
E
lub
e
.
3
Priorytety i wartościowania
Operatory posiadają priorytety, które są na stałe zakodowane w
analizatorze składni. Priorytety te decydują o kolejności, w jakiej
są wykonywane działania w złożonych wyrażeniach. Jak w innych
językach programowania, kolejność ta może być zmieniana za
pomocą nawiasów. Np.:
SELECT
1+2*4
;
oraz
SELECT
(1+2)*4
;
Operatory w PostgreSQL charakteryzują się kierunkiem nadawania
wartości. Może on być prawy lub lewy. Określa on kolejność
nadawania wartości operatorom o tym samym priorytecie.
Operatory arytmetyczne, jak dodawanie lub odejmowanie, są
wartościowane od lewej do prawej, np.: 1+2-3 jest równoważne
(1+2)-3. Inne operatory, jak np. operator równości, są
wartościowane od prawej do lewej. Wartość wyrażenia x=y=z jest
równa wartości wyrażenia x=(y=z).
Opis najpopularniejszych operatorów wraz z przykładami ich użycia
można znaleźć w pliku:
operatory.sql
Zobacz także:
dziwne.sql
4
PL/pgSQL
przegląd
Język PL/pgSQL jest ładowalnym językiem proceduralnym, który:
Umożliwia tworzenie funkcji i procedur wyzwalanych (triggerów)
Rozszerza SQL o struktury sterujące, podobne jak w językach
proceduralnych
PL/pgSQL daje możliwość definiowania własnych funkcji, które
można wykorzystywać wewnątrz bazy PostgreSQL. Instrukcja
CREATE FUNCTION
służy do definiowania nowych funkcji.
Postać ogólna:
CREATE FUNCTION nazwa (typ_arg1[,...])
RETURNS typ_wyniku
AS definicja_funkcji
LANGUAGE 'nazwa jezyka';
Przykład:
CREATE FUNCTION add_one (int4)
RETURNS int4 AS '
BEGIN RETURN $1 + 1 END
' LANGUAGE 'plpgsql';
Zobacz:
add_one.sql
5
PL/pgSQL
instalacja
Po utworzeniu funkcji, jej definicja jest zapisywana w bazie danych.
Kiedy funkcja jest wywoływana po raz pierwszy, definicja funkcji
jest kompilowana przez program obsługi do postaci wykonywalnej, a
następnie wykonana. Oznacza to, że możemy nie wiedzieć o błędzie
funkcji do czasu, kiedy spróbujemy jej użyć.
Każda baza danych może posiadać zdefiniowane dla niej języki
programowania, niezależnie od pozostałych baz (bezpieczeństwo).
Domyślnie bazy danych PostgreSQL nie obsługują żadnych języków
proceduralnych. Aby korzystać z języka PL/pgSQL należy
samodzielnie zainstalować program jego obsługi, np. tak:
createlang plpgsql template1;
Administrator bazy danych może dodać języki do bazy danych
template1
. W takim przypadku, wszystkie nowe bazy danych będą
posiadały domyślną obsługę tych języków.
6
PL/pgSQL
argumenty i deklaracje
PL/pgSQL to język blokowo-strukturalny, podobny do języka Pascal
lub C, z deklaracjami zmiennych i zakresami bloków. Każdy blok
składa się z opcjonalnej etykiety, deklaracji zmiennych i zamykają
go instrukcje otoczone słowami kluczowymi
BEGIN
i
END
. Składnia:
[etykieta]
[DECLARE deklaracje]
BEGIN instrukcje END;
Wielkość liter w tym języku nie ma znaczenia. Wszystkie słowa
kuczowe i nazwy zmiennych można zapisać z zastosowaniem liter
małych lub wielkich.
Funkcje PL/pgSQL mogą nie mieć argumentów lub mieć ich kilka.
Typy parametrów podaje się w nawiasach po nazwie funkcji. Każda
funkcja przechowywana w bazie musi zwracać wynik. Odwołania do
parametrów wewnątrz treści funkcji mają postać
$1
,
$2
,
...
, w
kolejności ich definiowania. W części deklaracji można parametrom
nadawać nazwy, np.:
nazwa ALIAS FOR $5;
Zobacz:
deklar.sql
7
PL/pgSQL
zmienne
Prosta deklaracja zmiennej jest następująca:
nazwa [CONSTANT] typ [NOT NULL] [:= wartosc];
Występowanie
CONSTANT
oznacza, że wartości zmiennej nie wolno
zmieniać. Musi ona posiadać wartość początkową.
NOT NULL
powoduje zgłoszenie błędu, gdy zmiennej przypiszemy
NULL
.
PostgreSQL pozwala deklarować tzw. zmienne złożone,
odpowiadające całym wierszom w określonej tabeli. Składnia:
nazwa tabela%ROWTYPE;
ROWTYPE
jest słowem kluczowym,
tabela
jest nazwą tabeli.
Wynikiem tej deklaracji jest zmienna zawierająca pola, po jednym
dla każdej kolumny tabeli, na podstawie której powstała. Aby
skorzystać z pól, należy zastosować składnię:
zmienna.pole
.
Innym rodzajem typu złożonego jest
RECORD
. Jest on podobny do
ROWTYPE
ale nie opiera się on na konkretnej tabeli. Typ ten jest
wypełniany wartościami w czasie wykonania. Przydatny w
definicjach procedur wyzwalanych (więcej o tym, później).
Zobacz:
zmienne.sql
8
PL/pgSQL
instrukcje przypisania
Podstawowa instrukcja przypisania w PL/pgSQLu ma następującą
postać:
referencja := wyrazenie;
referencja
jest nazwą zmiennej lub pola typu złożonego, np.
ROWTYPE
lub
RECORD
.
wyrazenie
jest zbudowane z dostępnych w
PosgreSQLu operacji, stałych, funkcji, zmiennych, konwersji typów.
W PL/pgSQLu istnieje możliwość przypisywania wartości całym
wierszom danych wartości. Służy do tego instrukcja:
SELECT wyrazenia INTO cel [FROM ... ];
W przypadku różnic typów, tam, gdzie to możliwe, PostgreSQL
zastosuje odpowiednie operacje konwersji. Wszystkie operacje
przypisania, nawet
:=
, są wykonywane przez serwer jako powyższa
instrukcja
SELECT
. Przez wykorzystanie klauzuli
FROM, ...
,
możemy zmiennym przypisać wartości obliczone na podstawie
zawartości bazy. Tylko pierwszy wiersz oddawany przez
SELECT
jest przypisywany zmiennej, reszta jest pomijana. Jeżeli
SELECT
nie zwraca żadnych wierszy, to zmienna
FOUND = false
.
Zobacz:
przypisania.sql
9
PL/pgSQL
wyjątki i komunikaty
Funkcja w PL/pgSQLu, prócz instrukcji
RETURN
, może także
zakończyć się w przypadku powstania warunków, które
uniemożliwiają kontynuowanie jej działania. Zamiast wartości
funkcja zwraca wówczas wyjątek. Wyjątek spowoduje wykonanie
zapisu w dzienniku zdarzeń i może spowodować, że funkcja
zakończy się natychmiast:
RAISE poziom 'format' [zmienna, ...];
PostgreSQL definiuje następujące trzy poziomy ważności wyjątków:
DEBUG
zapisuje komunikat w dzienniku (zwykle wyłączone)
NOTICE
zapisuje komunikat w dzienniku i wysyła do aplikacji
EXCEPTION
zapisuje komunikat w dzienniku i kończy funkcję
Poziom
DEBUG
stosowany do uzyskiwania dodatkowych informacji
podczas normalnego działania funkcji.
NOTICE
umożliwia
generowanie ostrzeżeń dla błędów, które nie są błędami fatalnymi.
EXCEPTION
stosuje się dla błędów fatalnych. Więcej w
dokumentacji.
Zobacz:
wyjatki.sql
10
PL/pgSQL
instrukcje warunkowe
W PL/pgSQLu istnieje kilka rodzajów instrukcji warunkowych. Są to
konstrukcje, które wykonują jeden lub kilka zbiorów instrukcji z w
zależności od wyniku testu. Najpopularniejsza jest
IF_THEN_ELSE
,
taka jak na językach programowania:
IF warunek THEN instrukcje
[ELSE instrukcje] END IF;
Kolejne to właściwie wyrażenia warunkowe, a nie instrukcje:
NULLIF(wejscie, wartosc);
Wynikiem jest
NULL
, jeżeli
wejscie=wartosc
,
wejscie
w
przeciwnym przypadku.
Następna, to dobrze znana instrukcja
CASE
:
CASE WHEN warunek THEN wyrazenie
[WHEN warunek THEN wyrazenie]
ELSE wyrazenie
END;
Zobacz:
warunki.sql
11
PL/pgSQL
pętle
PL/pgSQL posiada bogaty zestaw pętli iteracyjnych. Najprostszą jest
pętla nieskończona, z której można wyjść tylko za pomocą instrukcji
EXIT
:
[<<etykieta>>]
LOOP instrukcje
END LOOP;
<<infinite>>
LOOP n:=n+1;
EXIT infinite WHEN n>=10;
END LOOP;
W powyższym przykładzie instrukcja
EXIT
zakończy pętlę o
etykiecie
infinite
, gdy
n>=10
. Program przejdzie do wykonania
następnej instrukcji po pętli.
EXIT
może występować tylko
wewnątrz pętli.
Prócz powyższej, PL/pgSQL udostępnia również pętle
WHILE
i
FOR
o
sładni:
[<<etykieta>>]
WHILE wyra?enie
LOOP
instrukcje
END LOOP;
[<<etykieta>>]
FOR nazwa IN [REVERSE] od..do
LOOP
instrukcje
END LOOP;
Zobacz:
petle.sql
przyklad.sql
12
PL/pgSQL
zapytania dynamiczne
Dotychczasowe funkcje używały tylko operacji będących napisami
stałymi. Czasami zachodzi potrzeba zdefiniowania funkcji, której
sposób wykonania, tj. odwołania do wartości, kolumn, czy funkcji
będą wyliczane dynamicznie za każdym razem, gdy funkcja jest
wywoływana. Służy do tego instrukcja:
EXECUTE ciag_znakow;
Ciąg znaków, będący wykonywanym zapytaniem, można tworzyć
dynamicznie wewnątrz funkcji, za pomocą operatorów operatorów
manipulowania ciągami znaków. Należy zwrócić szczególną uwagę
na odpowiednie użycie apostrofów dla nazw i literałów wewnątrz
ciągu znaków.
Wszystkie nazwy tabel i kolumn powinny być przetwarzane za
pomocą funkcji
quote_ident
, natomiast wartości należy
przetwarzać za pomocą funkcji
quote_literal
.
Zobacz:
dynamiczne.sql
13
Funkcje SQL
Do tworzenia funkcji można używać także języka SQL. W tym celu
trzeba określić język procedury jako
'SQL'
i korzystać z instrukcji
języka SQL zamiast z instrukcji języka PL/pgSQL.
Instrukcje SQL przyjmują parametry, do kórych można się
odwoływać używając znaków
$1
,
$2
, ... . Nie są dostępne instrukcje
sterujące, dostępne w PL/pgSQLu. Można jedynie korzystać z
instrukcji SQL. Oznacza to, że o ile PL/pgSQL oferuje deklaracje
zmiennych, wartościowania warunkowe i pętle, to SQL oferuje
jedynie zastępowanie argumentów wywołania funkcji wartościami
wywołania. Wartością zwracaną przez funkcję SQL są dane zwracane
przez ostatnio wykonywaną instrukcję SQL, zwykle jest to
SELECT
.
Zaletą funkcji SQL jest to, że nie musimy ładować do bazy danych
obsługi języka PL/pgSQL. Dodatkowo funkcje SQL umożliwiają
zwracanie więcej niż jednego wiersza danych, jeżeli zadeklarujemy
typ zwracanej wartości jako
setof tabela
.
Zobacz:
fun_sql.sql
14
Procedury wyzwalane
Dotychczas poznaliśmy dwa mechanizmy zapewniające sprawdzanie
i zachowanie logiki bazy danych. Są nimi klucze obce oraz warunki
logiczne nałożone na tabelę w trakcie jej tworzenia. Oba te warunki
mają charakter statyczny i nie pozwalają na wiązanie konkretnych
akcji ze zdarzeniami zachodzącymi w bazie, a nie z jej stanem.
Takie wiązanie akcji ze zdarzeniami jest możliwe dzięki
procedurom wyzwalanym (ang. triggers). Za pomocą procedury
wyzwalanej, w PostgreSQLu, można spowodować, że wykona się
procedura zapamiętana w bazie danych, jeżeli dla określonej tabeli
będą podjęte takie działania jak
INSERT
,
UPDATE
oraz
DELETE
.
Składnia polecenia tworzącego procedurę wyzwalaną:
CREATE TRIGGER nazwa {BEFORE | AFTER}
{zdarzenie [OR ...]}
ON tabela FOR EACH {ROW | STATEMENT}
EXECUTE PROCEDURE funkcja(argumenty);
15
Procedury wyzwalane
cd
Wyzwalacz zadziała, gdy zajdzie określone zdarzenie (
INSERT
,
DELETE
lub
UPDATE
). Możemy zażądać by zadziałał on po (
AFTER
)
zajściu zdarzenia. Wówczas wywołana procedura będzie miała
dostęp do danych pierwotnych (
UPDATE
i
DELETE
) oraz do nowych
danych (
UPDATE
i
INSERT
). Możemy również zażądać by zadziałał
przed (
BEFORE
) zajściem zdarzenia. W takim przypadku możemy
zabezpieczyć przed aktualizacją lub zmienić dane, które mają być
wprowadzane lub aktualizowane.
Dla zdarzeń dotyczących wielu wierszy, spowodowanych pojedynczą
instrukcją, możemy wybrać: czy wyzwalacz ma działać dla każdego
aktualizowanego wiersza (
ROW
), czy raczej raz dla całej operacji
aktualizacji (
STATEMENT
).
Kiedy spełnione są określone warunki, wyzwalacz wykonuje specjalny
rodzaj procedury przechowywanej w bazie danych, wzawnej
procedurą wyzwalaną. Są one podobne do zwykłych funkcji
przechowywanych w bazie, lecz podlegają pewnym ograniczeniom.
16
Procedury wyzwalane
ograniczenia
Procedura wyzwalana jest składowana przez PostgreSQL jako
funkcja bez parametrów oraz o specjalnym typie wyniku
OPAQUE
.
Typ ten stosuje się dla funkcji zwracających wartości, których
PostgreSQL nie może bezpośrednio przetwarzać.
Procedura wywołana przez procedurę wyzwalaną, w reakcji na
zmiany w określonej tabeli, musi zwracać
NULL
lub wiersz
odpowiadający strukturze zmienianej tabeli.
Dla wyzwalaczy typu
AFTER
, które wykonuje się po operacji
UPDATE
, zaleca się, aby procedura wyzwalana zwracała wartość
NULL
.
Dla wyzwalaczy typ
BEFORE
, zwracany wynik wykorzystuje się do
sterowania aktualizacją, która ma być wykonana. Jeżeli procedura
wyzwalana zwraca
NULL
, operacja
UPDATE
nie jest wykonywana.
Jeżeli zwracany jest wiersz danych, jest on wykorzystany jako
źródło aktualizacji, dając procedurze okazję do zmiany danych
przed zatwierdzeniem ich w bazie danych.
Zobacz:
trigger1.sql