Menu

PostgreSQL CREATE FUNCTION i procedury składowane

CREATE FUNCTION definiuje funkcję wywoływaną w zapytaniach, napisaną w SQL albo PL/pgSQL. CREATE PROCEDURE definiuje procedurę uruchamianą przez CALL, która może po drodze zatwierdzać transakcje.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

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.

FunkcjaProcedura
UruchamianieSELECT f(...), w dowolnym miejscu zapytaniatylko CALL p(...)
Zwracawartość, wiersz albo zbiór wierszynic (albo parametry OUT/INOUT)
COMMIT / ROLLBACK w środkunietak, w PL/pgSQL, gdy nie jest wywołana w bloku transakcji
OdzawszePostgreSQL 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ące random() 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);.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij