CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; definiuje funkcję, którą zapytania mogą wywoływać jak wbudowaną. Ciało stoi między znacznikami $$, w zwykłym SQL albo w PL/pgSQL, języku proceduralnym PostgreSQL. CREATE PROCEDURE (PostgreSQL 11+) definiuje natomiast procedurę, którą uruchamiasz przez CALL.
with_tax(50) używa domyślnej stawki i zwraca 60; with_tax(50, 0.1) zwraca 55. Argumenty można też przekazywać po nazwie: with_tax(50, rate => 0.1).
Logika w PL/pgSQL
Użyj LANGUAGE plpgsql, gdy funkcja potrzebuje zmiennych, IF, pętli albo obsługi błędów. Zmienne trafiają do sekcji DECLARE, a kod stoi między BEGIN i END:
Paczka 4 kg kosztuje podstawowe 5; paczka ekspresowa 20 kg kosztuje (5 + 10 * 0.5) * 2 = 20. W PL/pgSQL RAISE NOTICE 'value: %', cost; wypisuje komunikat, a RAISE EXCEPTION przerywa z błędem.
Gdy logikę da się wyrazić zwykłym SQL, wybierz LANGUAGE sql: planer może wbudować prostą funkcję SQL w wywołujące zapytanie.
Zwracanie zbioru wierszy
RETURNS TABLE (...) tworzy funkcję, która zwraca wiersze. Wywołaj ją w FROM jak tabelę:
orders_of('ana') zwraca zamówienia 1 i 3. RETURNS SETOF orders zwraca całe wiersze tabeli orders zamiast wymienionych kolumn.
Od PostgreSQL 14 funkcja SQL może mieć ciało w standardowym SQL zamiast tekstu. PostgreSQL zapisuje je sparsowane i zapamiętuje tabele oraz kolumny, których używa, więc nie da się ich usunąć, dopóki funkcja istnieje:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
Procedura składowana i jej uruchamianie przez CALL
Procedura nie ma typu zwracanego; uruchamiasz ją przez CALL. Może zmieniać dane, a gdy CALL nie stoi w jawnym bloku transakcji, może w trakcie wykonać COMMIT. Tak przetwarzasz duże zadanie partiami.
Konto 1 kończy z 70, a konto 2 z 80. Przelew 200 naruszyłby ograniczenie CHECK na koncie 1, a błąd wycofuje obie aktualizacje.
| Funkcja | Procedura | |
|---|---|---|
| Uruchamianie | SELECT f(...), w dowolnym miejscu zapytania | tylko CALL p(...) |
| Zwraca | wartość, wiersz albo zbiór wierszy | nic (albo parametry OUT/INOUT) |
COMMIT / ROLLBACK w środku | nie | tak, w PL/pgSQL, gdy nie jest wywołana w bloku transakcji |
| Od | zawsze | PostgreSQL 11 |
Oznaczanie zmienności: IMMUTABLE, STABLE, VOLATILE
Etykieta zmienności mówi planerowi, co może założyć o funkcji. Domyślnie jest VOLATILE.
IMMUTABLE: wynik zależy tylko od argumentów i nigdy się nie zmienia (with_tax). Wymagane dla funkcji używanych w wyrażeniu indeksu.STABLE: wynik może się zmieniać między poleceniami, ale nie w obrębie jednego, na przykład w funkcji, która czyta tabele (orders_of).VOLATILE: wszystko inne, na przykład funkcje używającerandom()albo zmieniające dane. PostgreSQL wywołuje je od nowa dla każdego wiersza.
Zła etykieta daje złe wyniki: funkcja IMMUTABLE, która czyta tabelę, może zwracać nieaktualne wartości z indeksu albo z zapamiętanego planu.
Zastępowanie, przeciążanie i usuwanie funkcji
CREATE OR REPLACE FUNCTION zmienia ciało istniejącej funkcji, ale nie jej typ zwracany; w tym celu najpierw ją usuń. PostgreSQL rozpoznaje funkcję po nazwie i typach argumentów, więc f(int) i f(text) to dwie funkcje. Żeby usunąć jedną z dwóch funkcji o tej samej nazwie, podaj typy argumentów:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
Rozdział o PL/pgSQL opisuje pętle, kursory, wyjątki i dynamiczny SQL przez EXECUTE. Żeby uruchamiać funkcję automatycznie przy zmianie wierszy, podepnij ją do wyzwalacza.
Najczęściej zadawane pytania
Czym różni się funkcja od procedury w PostgreSQL?
Funkcja zwraca wartość i jest wywoływana wewnątrz zapytania (SELECT f(x)). Procedura, dodana w PostgreSQL 11, jest uruchamiana samodzielnie przez CALL i może wykonać COMMIT albo ROLLBACK w swoim ciele; funkcja zawsze działa w transakcji wywołującego.
Co oznacza $$ w funkcji PostgreSQL?
$$ to cytowanie dolarowe: oznacza początek i koniec literału tekstowego, więc ciało funkcji może zawierać apostrofy bez ich escapowania. Możesz też dodać znacznik, jak w $body$ ... $body$.
Jak zobaczyć kod funkcji w PostgreSQL?
W psql \df wypisuje funkcje, a \sf name pokazuje definicję funkcji. W SQL SELECT pg_get_functiondef('name'::regproc); zwraca pełne polecenie CREATE OR REPLACE FUNCTION.
Czy funkcja PostgreSQL może zwrócić wiele wierszy?
Tak. Zadeklaruj ją jako RETURNS TABLE (col type, ...) albo RETURNS SETOF type i wywołaj w klauzuli FROM jak tabelę: SELECT * FROM f(1);.