Procedury składowane w PostgreSQL/Zarządzanie procedurami
PostgreSQL obsługuje przeciążanie nazw funkcji - w bazie może istnieć wiele procedur o tej samej nazwie pod warunkiem, że mają różne sygnatury. Sygnaturą jest nazwa funkcji oraz typy argumentów wejściowych, ale już typy wynikowe nie grają roli. Nie mogą więc istnieć dwie funkcje o tej samej nazwie i argumentach, ale różnych typach wynikowych.
Należy pamiętać, że w przypadku utworzenia funkcji w języku PL/pgSQL lub SQL w bazie danych pamiętany jest również jej pełny kod źródłowy, łącznie z komentarzami.
Tworzenie nowej funkcji
[edytuj]Składnia polecenia:
CREATE [ OR REPLACE ] FUNCTION nazwa ( lista_argumentów )
[ RETURNS typ zwracanej wartości ]
{ LANGUAGE nazwa języka
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| COST koszt wykonywania
| ROWS liczba wierszy wyjściowych
| SET parametr { TO wartość | = wartość | FROM CURRENT }
| AS 'treść procedury'
} ...
- nazwa
- Nazwa procedury
- lista argumentów
- Nazwy i typy argumentów przyjmowanych przez procedurę
- typ zwracanej wartości
- Dla procedur zwracających pojedyncze rekordy powinna to być nazwa typu już istniejącego w systemie. Dla procedur zwracających nazwa typu mus być poprzedzona słówkiem SETOF lub też, od wersji 9.0, można jako typ podać definicję tabeli TABLE ( lista kolumn i typów ).
- nazwa języka
- napis plpgsql lub SQL (może być w cudzysłowie lub nie)
- IMMUTABLE, STABLE, VOLATILE [domyślnie]
- Określa, w jaki sposób procedura odwołuje się do bazy danych; odpowiednio: bez odczytów ani zapisów, tylko odczytuje dane, zmienia dane.
- CALLED ON NULL INPUT [domyślnie], RETURNS NULL ON NULL INPUT lub krócej STRICT
- Decyduje, czy funkcja jest wykonywana, gdy jakiś argument ma wartość NULL, czy od razu zwracane jest NULL. Czytaj więcej
- SECURITY INVOKER [domyślnie] lub SECURITY DEFINER (słówko EXTERNAL jest nieobowiązkowe) - uprawnienia
- Ustala z jakimi prawami uruchamiana jest procedura - czy z prawami użytkownika który wywołuje funkcję, czy też z takimi prawami, jakie miał użytkownik tworzący procedurę.
- COST koszt wykonywania
- Ustala przewidywany koszt wykonywania funkcji; można w ograniczonym zakresie pomóc w optymalizacji zapytań zawierających wywołanie funkcji.
- ROWS liczba wierszy wyjściowych
- Ustala przewidywaną liczbę zwracanych wierszy; może wpłynąć na plan wykonywania zapytań zawierających wywołanie funkcji.
- SET parametr { TO wartość | = wartość | FROM CURRENT }
- Lista parametrów bazy danych, jakie mają być ustawione przed wejściem do procedury; odpowiada wykonaniu instrukcji SET LOCAL ....
- treść procedury
- Napis zawierający kod źródłowy procedury. Oprócz zwykłych cudzysłowów można używać dużo wygodniejszej formy $$ treść procedury $$ lub $id$ treść procedury $id$.
Zastępowanie funkcji
[edytuj]Użycie zamiast CREATE FUNCTION nazwa ... polecenia CREATE OR REPLACE FUNCTION nazwa ... powoduje zastąpienie istniejącej procedury.
Jest to jednak możliwe tylko, jeśli nowa procedura nie zmienia zwracanego typu.
UWAGA: to działanie może mieć bardzo poważne konsekwencje w działającym systemie!
Kasowanie funkcji
[edytuj]Usunięcie istniejącej procedury wymaga sygnatury funkcji, tj. podania nazwy oraz typów argumentów: DROP FUNCTION nazwa(typ1, typ2, typ3).
Można podawać skrócone nazwy typów w przypadku varchar, char i numeric, pomijając liczbę znaków oraz precyzję liczby.
UWAGA: to działanie może mieć bardzo poważne konsekwencje w działającym systemie!
Zmiana funkcji
[edytuj]Instrukcja ALTER FUNCTION nazwa(argumenty) ... pozwala zmienić:
- nazwę funkcji;
- schemat do którego należy;
- właściciela;
- własność IMMUTABLE, STABLE, VOLATILE;
- własność CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT/STRICT;
- uprawnienia SECURITY INVOKER, SECURITY DEFINER;
- koszt COST;
- liczbę wierszy ROWS;
- parametry SET.
Informacje o funkcji
[edytuj]Z poziomu standardowego narzędzia konsolowego psql można przeglądać zdefiniowane procedury poleceniem \df lub \df+, który wyświetla m.in. kod źródłowy.
Procedury są zapisywane w systemowej tabeli pg_proc. Aby odczytać OID funkcji można zrzutować nazwę na typ regproc, ale to zadziała tylko, gdy nazwa nie jest przeciążona. W przeciwnym razie należy rzutować pełną sygnaturę funkcji na regprocedure.
CREATE OR REPLACE FUNCTION funkcja(arg1 text, arg2 integer, arg3 char = '?') RETURNS void AS $$
BEGIN
-- ...
END;
$$ LANGUAGE 'plpgsql';
SELECT 'funkcja'::regproc;
SELECT 'funkcja(text, integer, char)'::regprocedure;
SELECT * FROM pg_proc WHERE oid = 'funkcja'::regproc;
Przykładowy dane rekordu dla funkcji:
kolumna | wartość
----------------+---------
proname | funkcja
pronamespace | 2200
proowner | 16384
prolang | 11574
procost | 0.1
prorows | 0
provariadic | 0
proisagg | f
proiswindow | f
prosecdef | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 3
pronargdefaults | 1
prorettype | 23
proargtypes | 25 23 1042
proallargtypes |
proargmodes |
proargnames | {napis,liczba,znak}
proargdefaults | ({CONST :consttype 1042 :consttypmod -1 :constlen -1 :constbyval false :constisnull false :location 75 :constvalue 5 [ 20 0 0 0 63 ]})
prosrc |
| BEGIN
| RETURN 42;
| END;
|
probin |
proconfig | {search_path=public}
proacl |
Funkcje pomocnicze
[edytuj]- pg_get_functiondef(oid funkcji) => text
- zwraca instrukcję SQL CREATE FUNCTION ..., pozwalającą odtworzyć tę procedurę.
- pg_get_function_arguments(oid funkcji) => text
- zwraca pełną listę argumentów: tryby [OUT/INOUT], nazwy, typy, wartości domyślne
- pg_get_function_identity_arguments(oid funkcji) => text
- zwraca pełną listę argumentów bez wartości domyślnych
- pg_get_function_result(oid_funkcji) => text
- zwraca nazwę typu wynikowego funkcji
$ SELECT pg_get_functiondef('funkcja'::regproc)
pg_get_functiondef
----------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.funkcja(napis text, liczba integer, znak character DEFAULT '?'::bpchar)+
RETURNS integer +
LANGUAGE plpgsql +
COST 0.1 +
SET search_path TO public +
AS $function$ +
BEGIN +
RETURN 42; +
END; +
$function$ +
$ SELECT pg_get_function_arguments('funkcja'::regproc);
pg_get_function_arguments
----------------------------------------------------------------
napis text, liczba integer, znak character DEFAULT '?'::bpchar
(1 wiersz)
$ SELECT pg_get_function_identity_arguments('funkcja'::regproc);
pg_get_function_identity_arguments
--------------------------------------------
napis text, liczba integer, znak character
$ SELECT pg_get_function_result('funkcja'::regproc);
pg_get_function_result
------------------------
integer