Menu

PostgreSQL CREATE FUNCTION und Stored Procedures

CREATE FUNCTION definiert eine Funktion, die du in Abfragen aufrufst, geschrieben in SQL oder PL/pgSQL. CREATE PROCEDURE definiert eine Routine, die du mit CALL startest und die unterwegs Transaktionen committen kann.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

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.

FunktionProzedur
Aufruf mitSELECT f(...), überall in einer Abfragenur CALL p(...)
Lieferteinen Wert, eine Zeile oder eine Menge von Zeilennichts (oder OUT/INOUT Parameter)
COMMIT / ROLLBACK im Rumpfneinja, in PL/pgSQL, wenn nicht in einem Transaktionsblock aufgerufen
Seitschon immerPostgreSQL 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, die random() 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);.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's