Wikibooks

Procedury składowane w PostgreSQL/Zarządzanie procedurami

< Procedury składowane w PostgreSQL

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
Źródło: „https://pl.wikibooks.org/w/index.php?title=Procedury_składowane_w_PostgreSQL/Zarządzanie_procedurami&oldid=399216”