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