CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; definiert eine Funktion, die Abfragen wie eine eingebaute aufrufen können. Der Rumpf steht zwischen $$ Markierungen, in reinem SQL oder in PL/pgSQL, der prozeduralen Sprache von PostgreSQL. CREATE PROCEDURE (PostgreSQL 11+) definiert dagegen eine Routine, die du mit CALL startest.
with_tax(50) nimmt den Standardsatz und liefert 60; with_tax(50, 0.1) liefert 55. Argumente lassen sich auch über den Namen übergeben: with_tax(50, rate => 0.1).
Logik in PL/pgSQL schreiben
Nimm LANGUAGE plpgsql, wenn die Funktion Variablen, IF, Schleifen oder Fehlerbehandlung braucht. Variablen stehen in einem DECLARE Abschnitt, der Code zwischen BEGIN und END:
Ein Paket mit 4 kg kostet den Grundpreis von 5; ein Expresspaket mit 20 kg kostet (5 + 10 * 0.5) * 2 = 20. In PL/pgSQL gibt RAISE NOTICE 'value: %', cost; eine Meldung aus, und RAISE EXCEPTION bricht mit einem Fehler ab.
Wenn sich die Logik in reinem SQL ausdrücken lässt, nimm lieber LANGUAGE sql: Der Planer kann eine einfache SQL-Funktion in die aufrufende Abfrage einbetten.
Eine Menge von Zeilen zurückgeben
Mit RETURNS TABLE (...) liefert eine Funktion Zeilen. Ruf sie in FROM wie eine Tabelle auf:
orders_of('ana') liefert die Bestellungen 1 und 3. RETURNS SETOF orders liefert stattdessen ganze Zeilen der Tabelle orders statt der aufgeführten Spalten.
Seit PostgreSQL 14 kann eine SQL-Funktion einen Rumpf in Standard-SQL statt eines Strings haben. PostgreSQL speichert ihn geparst und merkt sich die Tabellen und Spalten, die er nutzt. Diese lassen sich dann nicht löschen, solange die Funktion existiert:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
Eine Stored Procedure anlegen und mit CALL starten
Eine Prozedur hat keinen Rückgabetyp; du startest sie mit CALL. Sie kann Daten ändern, und wenn der CALL nicht in einem expliziten Transaktionsblock steht, kann sie zwischendurch COMMIT ausführen. So arbeitest du einen großen Job in Portionen ab.
Konto 1 endet mit 70 und Konto 2 mit 80. Eine Überweisung von 200 würde den CHECK Constraint von Konto 1 verletzen, und der Fehler rollt beide Updates zurück.
| Funktion | Prozedur | |
|---|---|---|
| Aufruf mit | SELECT f(...), überall in einer Abfrage | nur CALL p(...) |
| Liefert | einen Wert, eine Zeile oder eine Menge von Zeilen | nichts (oder OUT/INOUT Parameter) |
COMMIT / ROLLBACK im Rumpf | nein | ja, in PL/pgSQL, wenn nicht in einem Transaktionsblock aufgerufen |
| Seit | schon immer | PostgreSQL 11 |
Die Volatilität angeben: IMMUTABLE, STABLE, VOLATILE
Das Volatilitäts-Label sagt dem Planer, was er über eine Funktion annehmen darf. Standard ist VOLATILE.
IMMUTABLE: Das Ergebnis hängt nur von den Argumenten ab und ändert sich nie (with_tax). Pflicht für Funktionen in einem Index-Ausdruck.STABLE: Das Ergebnis kann sich zwischen Anweisungen ändern, aber nicht innerhalb einer, etwa bei einer Funktion, die Tabellen liest (orders_of).VOLATILE: alles andere, etwa Funktionen, dierandom()nutzen oder Daten ändern. PostgreSQL ruft sie für jede Zeile neu auf.
Ein falsches Label führt zu falschen Ergebnissen: Eine IMMUTABLE Funktion, die eine Tabelle liest, kann veraltete Werte aus einem Index oder einem gecachten Plan liefern.
Eine Funktion ersetzen, überladen und löschen
CREATE OR REPLACE FUNCTION ändert den Rumpf einer bestehenden Funktion, aber nicht ihren Rückgabetyp; dafür musst du sie zuerst löschen. PostgreSQL erkennt eine Funktion an ihrem Namen und ihren Argumenttypen, f(int) und f(text) sind also zwei Funktionen. Um eine von zwei gleichnamigen Funktionen zu löschen, gibst du die Argumenttypen an:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
Das Kapitel zu PL/pgSQL dokumentiert Schleifen, Cursor, Exceptions und dynamisches SQL mit EXECUTE. Um eine Funktion automatisch auszuführen, wenn sich Zeilen ändern, hängst du sie an einen Trigger.
Häufig gestellte Fragen
Was ist der Unterschied zwischen einer Funktion und einer Prozedur in PostgreSQL?
Eine Funktion liefert einen Wert und wird innerhalb einer Abfrage aufgerufen (SELECT f(x)). Eine Prozedur, die es seit PostgreSQL 11 gibt, läuft für sich mit CALL und kann in ihrem Rumpf COMMIT oder ROLLBACK ausführen; eine Funktion läuft immer in der Transaktion des Aufrufers.
Was bedeutet $$ in einer PostgreSQL Funktion?
$$ ist Dollar Quoting: Es markiert Anfang und Ende eines String-Literals, sodass der Funktionsrumpf einfache Anführungszeichen enthalten kann, ohne sie zu escapen. Du kannst auch ein Tag ergänzen, etwa $body$ ... $body$.
Wie sehe ich den Code einer PostgreSQL Funktion?
In psql listet \df Funktionen auf, und \sf name gibt die Definition einer Funktion aus. In SQL liefert SELECT pg_get_functiondef('name'::regproc); die vollständige CREATE OR REPLACE FUNCTION Anweisung.
Kann eine PostgreSQL Funktion mehrere Zeilen zurückgeben?
Ja. Deklariere sie mit RETURNS TABLE (col type, ...) oder RETURNS SETOF type und ruf sie in der FROM Klausel wie eine Tabelle auf: SELECT * FROM f(1);.