Kurze Einführung in PL/pgSQL

Michael Roth; Inge Schestag

Quelle: Postgres Dokumentation

Aufbau eines PL/pgSQL-Blockes

PL/pgSQL ist blockorientiert, wobei sich ein Block in die folgenden Teile gliedert:

• Deklaration der Variablen, eingeleitet durch das Schlüsselwort DECLARE (dieser Block ist optional)
• Anweisungsblock, eingeleitet durch das Schlüsselwort BEGIN, das Blockende wird gekennzeichnet durch die Anweisung END;
• Fehler-Behandlung (Exception-Handling) innerhalb des Anweisungsblocks, eingeleitet durch das Schlüsselwort EXCEPTION (dieser Block ist optional)

DECLARE
  <Datendeklaration>
BEGIN
  <SQL- und PL/pgSQL-Code>
EXCEPTION
  <PL/pgSQL-Code>
END;

Ausführen eines anonymen Blocks

Ein anonymer Block ist eine transiente, anonyme Funktion in einer prozeduralen Sprache. Zur Ausführung eines anonymen Blocks wird das Schlüsselwort DO verwendet.

Beispiel:

DO $label$
DECLARE
  <Datendeklaration>
BEGIN
  <SQL- und PL/pgSQL-Code>
EXCEPTION
  <PL/pgSQL-Code>
END;
$label$

Das label dient dabei zur Eingrenzung des Blocks. Als label kann ein beliebiger String verwendet werden, so lange das erste und letzte Zeichen ein $ ist. Die beiden Strings müssen allerdings identisch sein!

"Hello, World!" in PL/pgSQL:

DO $helloworld$
BEGIN
  RAISE NOTICE 'Hello, World!';
END;
$helloworld$

Datendeklaration

Jede Variable, die in einem PL/pgSQL-Block benutzt wird, muss zuvor deklariert werden. Die folgenden Datentypen stehen u.a. zur Verfügung: VARCHAR(n), NUMERIC, INTEGER, DATE, und BOOLEAN (Vollständige Liste hier).

Syntax:

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

Beispiele:

DECLARE
  s varchar(20);
  n numeric(6,2);
  x boolean;

Diese Variablen haben den Anfangswert NULL, wenn sie nicht initialisiert werden.

Initialisierung von Variablen durch den Zuweisungsoperator :=

Beispiele:

-- Textvariable:
s varchar(5) := 'ABCDE';
-- Numerische Variable (Hier mit der optionalen Ergänzung, dass der Wert nie NULL sein darf):
n1 numeric(6,2) not null := 0;
-- Boolesche Variable:
x boolean := TRUE;
-- Arithmetischer Ausdruck:
n2 numeric(6,2) := n1 + 100.00;
-- Deklaration von Konstanten:
n CONSTANT numeric(6,2) := 100;
-- Statt einer Zuweisung kann auch DEFAULT verwendet werden:
n3 numeric(10,2) DEFAULT 0;

Die Spaltennamen der Datenbank-Tabelle müssen nicht deklariert werden. Mit dem Zusatz %TYPE kann man den Datentyp einer Tabellespalte ermitteln:

  n e_nr%TYPE;

Geschachtelte PL/pgSQL-Blöcke

Schachtelungen sind im Anweisungsblock und Exception-Teil möglich. Eine Variable ist lokal bzgl. eines Blocks, wenn sie in ihm deklariert ist. Eine Variable ist global bzgl. eines Blocks, wenn sie nicht in ihm, aber in einem übergeordneten Block deklariert ist.

Arithmetische Operatoren und Vergleichsoperatoren

stehen entsprechend der jeweiligen SQL-Operatoren zur Verfügung.

Kontrollstrukturen: Verzweigungen

Einfaches if:

IF <Bedingung> THEN
  <Anweisungsblock>
END IF;

Auswahl mit if-then:

IF <Bedingung> THEN
  <Anweisungsblock 1>
ELSE
  <Anweisungsblock 2>
END IF;

Komplexe Auswahl mit if-else if-then:

IF <Bedingung 1> THEN
  <Anweisungsblock 1>
ELSIF <Bedingung 2> THEN
  <Anweisungsblock 2>
ELSIF <Bedingung 3> THEN
  <Anweisungsblock 3>
ELSE
  <Anweisungsblock 4> ]
END IF;

Kontrollstrukturen: Schleifen

Es gibt drei grundsätzliche Schleifenarten:

loop ... end loop; --bedingungslose Schleife
for <Zählbereich> loop ... end loop; --Zählschleife
while <Bedingung > loop ... end loop; --bedingte Schleife

Anweisung EXIT:

EXIT [ <Label> ] [ WHEN <Ausdruck> ];

Ist kein Label angegeben, wird die innerste Schleife relativ zum EXIT beendet und mit der auf END LOOP folgenden Anweisung weiter gemacht.

Ist ein Label angegeben, so wird die Schleife bzw. der Block abgebrochen, der unmittelbar auf dieses Label folgt.

Mittels WHEN lässt sich eine Bedingung angeben, die erfüllt sein muss damit es zum EXIT kommt.

Beispiel für eine Zählschleife:

FOR i IN 1..10 LOOP
  -- i nimmt Werte 1,2,3,4,5,6,7,8,9,10 an
END LOOP;

SQL-Befehle innerhalb von PL/pgSQL

Cursor Konzept

Cursor bieten die Möglichkeit, das Ergebnis einer Abfrage zeilenweise durch zu iterieren. Ein Cursor wird als Variable im DECLARE Abschnitt angelegt und kann dann später verwendet werden.

Einen Cursor deklarieren

Syntax:

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

Mit SCROLL bzw. NO SCROLL kann angegeben werden, ob der Cursor vorwärts und rückwärts bzw. nur vorwärts iteriert werden kann.

Wird eine query mit angegeben, so ist der Cursor fest mit dieser Abfrage verbunden, es handelt sich also um einen bound cursor.

Beispiel:

c1 SCROLL CURSOR FOR SELECT * FROM eteil;

Ein Cursor kann auch als unbound cursor deklariert werden, in dem bei der Deklaration nur der Name angegeben wird:

c2 CURSOR;

Einen Cursor verwenden

Bevor ein Cursor verwendet werden kann, muss dieser geöffnet werden. Das Öffnen unterscheidet sich, je nachdem ob es sich um einen bound oder unbound Cursor handelt:

open c1; --bound cursor
open c2 FOR SELECT * FROM flug; --unbound cursor

Mittels FETCH kann die jeweils nächste Zeile eines geöffneten Cursors in eine oder mehrere Variablen geladen werden:

FETCH c1 INTO rowvar; --rowvar ist vom Typ record
FETCH c2 INTO foo, bar, baz;

Ein nachfolgendes FETCH liefert die nächste Zeile des Ergebnisses.

Eine spezielle Variable FOUND wird auf false gesetzt, falls es keine weitere Zeile zum Einlesen mehr gibt. So lässt sich mit einem Cursor über eine gesamte Tabelle iterieren.

Beispiel:

declare
  zeile record;
  c1 cursor for select * from eteil;
begin
   open c1;
   loop
    fetch c1 into zeile;
    if not found then 
       return;
     end if;
     raise info 'Zeile = %', zeile;
   end loop;
 end;

Schließen eines Cursors

Mit CLOSE c1 wird der geöffnete Cursor c1 wieder geschlossen.

Iteration mittels FOR-Schleife

Alternativ zum oben genannten Vorgehen kann auch eine FOR-Schleife verwendet werden, um über einen Cursor zu iterieren:

declare
  zeile record;
  c1 cursor for select * from eteil;
begin
  for zeile in c1 loop
    raise info 'Zeile = %', zeile;
  end loop;
end;

Wichtig: Die FOR-Schleife öffnet und schließt den Cursor automatisch. Der Cursor darf zu Beginn der Schleife nicht bereits geöffnet sein.

Weitere Informationen zu Cursors hier.

SQL-Abfragen mit Zuweisung einer einzigen Ergebniszeile

Sie können SQL-Abfragen in einem PL/pgSQL Block starten und das Ergebnis in einer oder mehreren Variablen speichern. Die Datentypen der Variablen können dabei atomar oder aber vom Typ record, also einer Tabellenzeile, sein.

Wichtig: Es wird immer nur die erste Zeile des Ergebnisses gespeichert!

Zuweisung mittels select

Hierbei werden Werte über Spaltennamen aus der Datenbank ermittelt, die mit Hilfe des Schlüsselwortes INTO der oder den entsprechenden Variablen zugewiesen werden.

Beispiel:

select count(*) into anzahl from eteil;

Mit dieser Syntax kann man sich insbesondere eine Cursordeklaration sowie das zeilenweise Iterieren über den Cursor ersparen, wenn man sicher ist, dass die Ergebnismenge des selects aus maximal einer Zeile besteht.

Weitere Informationen hier.

INSERT und UPDATE Befehle

SQL-Inserts und SQL-Updates können als reguläre Anweisungen in PL/pgSQL verwendet werden. Weitere Informationen in der Postgres Doku.

Bildschirmausgaben

Ausgaben aus einem PL/pgSQL Programm heraus können mittels RAISE getätigt werden. Die Syntax ist folgende:

RAISE [ level ] 'message' [, expression [, ... ]]

Als level kann angegeben werden: DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION, wobei EXCEPTION einen Fehler auslöst und im Regelfall die bestehende Transaktion beendet.

Typischerweise erscheinen Ausgaben ab dem Level LOG tatsächlich in der Ausgabe. Sollte dies bei Ihnen nicht der Fall sein, verwenden Sie INFO oder NOTICE.

Die Nachricht message wird auf dem Bildschirm ausgegeben. Sollen Variablen oder Ausdrücke ausgegeben werden, so muss in der message der Platzhalter % verwendet werden. Die auszugebenden Variablen bzw. Ausdrücke müssen nach der message durch Komma getrennt angegeben werden:

RAISE INFO 'a = % b = % a+b = %', a, b, a+b;

Stored Procedures & Trigger in Postgres

Stored Procedures

Sowohl Stored Procedures (kein Rückgabewert) als auch Stored Functions werden als FUNCTION deklariert. Eine Stored Procedure muss ebenso wie eine Stored Function immer die RETURNS-Klausel enthalten. Rückgabetyp ist dann void:

... RETURNS void AS  $$ ...

Syntax:

CREATE FUNCTION <proc_name>(<Parameter1> <Datentyp>, <Parameter2> <Datentyp>,...) RETURNS integer
AS $label$
DECLARE
  <Datendeklaration>
BEGIN
  <SQL- und PL/pgSQL-Code>
EXCEPTION
  <PL/pgSQL-Code>
END;
$label$
LANGUAGE plpgsql;

Beispiel;

CREATE OR REPLACE FUNCTION test()
  RETURNS integer
  AS $function$
  begin
    return 17;
  end;
  $function$
  LANGUAGE plpgsql;

Syntax Aufruf – im interaktiven SQL (z.B. psql-Shell oder pgAdmin):

select test();

Syntax Aufruf – im PL/pgSQL-Block:

test();

Syntax Löschen einer Stored Procedure:

drop function test();

Trigger in Postgres

Trigger sind im Wesentlichen Listener, die bei Eintreten eines Events eine Aktion ausführen. Mögliche Events sind die Update-Operatoren insert, update, delete oder auch truncate auf einer beliebigen Tabelle bzw. View.

Ein Trigger kann entweder vor (before), nach (after) oder aber anstatt (instead of) des Events "feuern", d.h. die Aktion ausführen. Die auszuführende Aktion wird in einer Stored Function beschrieben, deren Rückgabedatentyp als trigger deklariert sein muss.

In PL/pgSQL sind, im Gegensatz zu Oracle PL/SQL, die Deklarationen von Triggerkopf und Triggerrumpf voneinander getrennt.

Beispiel einer Triggerfunktion:

CREATE OR REPLACE FUNCTION triggerfunc() RETURNS trigger
AS $function$
BEGIN
  RAISE INFO 'Neuer Datensatz: %', NEW;
  RETURN NEW;
  END;
$function$
LANGUAGE plpgsql;

Wichtig ist der Rückgabedatentyp trigger.

Der eigentliche Trigger wird getrennt von der Funktion deklariert:

CREATE trigger testtrigger AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE triggerfunc();

In einem FOR EACH-Trigger können in der Trigger Function bzw. in der WHEN-Klausel der Triggerdeklaration die Variablen OLD.<Columnname> bzw. NEW.<Columnname> verwendet werden, um auf bereits bestehende Spaltenwerte der entsprechenden Spalten bzw. neu zu generierende Spaltenwerte vor- bzw. nach Ausführung des Events zu zu greifen. Wird ein Trigger für mehr als einen der drei Update-Operatoren definiert, so kann in der Trigger Function bedingt das tatsächlich auslösende Event berücksichtigt werden:

IF (TG_OP = 'DELETE'|'INSERT'|'UPDATE') THEN
  ...
END IF;

Hierbei ist TG_OP eine von Postgres zur Verfügung gestellte Systemvariable, ebenso wie die OLD.- bzw. NEW.-Variablen.