|
|
# Kurze Einführung in PL/pgSQL (Postgres PL/SQL)
|
|
|
tbd |
|
|
\ No newline at end of file |
|
|
# Kurze Einführung in PL/pgSQL
|
|
|
|
|
|
_Michael Roth_; _Inge Schestag_
|
|
|
|
|
|
_Quelle: [Postgres Dokumentation](https://www.postgresql.org/docs/current/plpgsql.html)_
|
|
|
|
|
|
## 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)
|
|
|
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
DO $helloworld$
|
|
|
BEGIN
|
|
|
RAISE NOTICE 'Hello, World!';
|
|
|
END;
|
|
|
$helloworld$
|
|
|
```
|
|
|
|
|
|
## Datendeklaration
|
|
|
|
|
|
Jede Variable, die in einem PL/pgSQL-Block benutzt wird, muß zuvor deklariert werden. Die folgenden Datentypen stehen u.a. zur Verfügung: VARCHAR(n), NUMERIC, INTEGER, DATE, und BOOLEAN. (Vollständige Liste [hier](https://www.postgresql.org/docs/current/datatype.html))
|
|
|
|
|
|
Syntax:
|
|
|
```sql
|
|
|
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];
|
|
|
```
|
|
|
|
|
|
Beispiele:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
-- 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:
|
|
|
```sql
|
|
|
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``:
|
|
|
```sql
|
|
|
IF <Bedingung> THEN
|
|
|
<Anweisungsblock>
|
|
|
END IF;
|
|
|
```
|
|
|
|
|
|
Auswahl mit ``if-then``:
|
|
|
```sql
|
|
|
IF <Bedingung> THEN
|
|
|
<Anweisungsblock 1>
|
|
|
ELSE
|
|
|
<Anweisungsblock 2>
|
|
|
END IF;
|
|
|
```
|
|
|
Komplexe Auswahl mit ``if-else if-then``:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
loop ... end loop; --bedingungslose Schleife
|
|
|
for <Zählbereich> loop ... end loop; --Zählschleife
|
|
|
while <Bedingung > loop ... end loop; --bedingte Schleife
|
|
|
```
|
|
|
|
|
|
**Anweisung EXIT**:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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](https://www.postgresql.org/docs/current/plpgsql-cursors.html).
|
|
|
|
|
|
### 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 Variable kann 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:
|
|
|
```sql
|
|
|
select count(*) into anzahl from eteil;
|
|
|
```
|
|
|
|
|
|
Mit dieser Syntax kann man sich insbesondere eine Cursordeklaration sowie das zeilenweise Iterieren über den Cursor ersparen.
|
|
|
|
|
|
Weitere Informationen [hier](https://www.postgresql.org/docs/current/sql-selectinto.html).
|
|
|
|
|
|
### 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](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT).
|
|
|
|
|
|
## Bildschirmausgaben
|
|
|
|
|
|
Ausgaben aus einem PL/pgSQL Programm heraus können mittels ``RAISE`` getätigt werden. Die Syntax ist folgende:
|
|
|
```sql
|
|
|
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:
|
|
|
|
|
|
```sql
|
|
|
RAISE INFO 'a = % b = % a+b = %', a, b, a+b;
|
|
|
```
|
|
|
|
|
|
## Stored Procedures & Trigger in Postgres
|
|
|
|
|
|
### Stored Procedures
|
|
|
|
|
|
Syntax:
|
|
|
```sql
|
|
|
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;
|
|
|
```sql
|
|
|
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):
|
|
|
```sql
|
|
|
select test();
|
|
|
```
|
|
|
|
|
|
**Syntax Aufruf – im PL/pgSQL-Block**:
|
|
|
```sql
|
|
|
test();
|
|
|
```
|
|
|
|
|
|
**Syntax Löschen** einer Stored Procedure:
|
|
|
```sql
|
|
|
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 sein.
|
|
|
|
|
|
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:
|
|
|
```sql
|
|
|
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:
|
|
|
```sql
|
|
|
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 zugreifen.
|
|
|
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:
|
|
|
```sql
|
|
|
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. |
|
|
\ No newline at end of file |