Procedury składowane w PostgreSQL/Zarządzanie procedurami

Z Wikibooks, biblioteki wolnych podręczników.

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