10:42, 26. Nov. 2010

E3A
PostgreSQL
Funktionen in PostgreSQL
Inhaltsverzeichnis
1 Funktionen mit SQL
1.1 ohne Rückgabewert . .
1.2 mit Rückgabewert . .
1.3 mehrere Parameter . .
1.4 Rückgabewert Tabelle
1.5 mehrzeiliges Ergebnis .
1.5.1 setof . . . . . .
1.5.2 setof record . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2 Funktionen mit PL/pgSQL
2.1 Aufbau einer Funktion . . . . . . . . . . . . . . . . . . . . . . . .
2.1.1 Funktionscode . . . . . . . . . . . . . . . . . . . . . . . .
2.1.2 Variablen . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1.3 Fehler abfragen . . . . . . . . . . . . . . . . . . . . . . . .
2.2 Vergleiche . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.3 Schleifen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.3.1 LOOP . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.3.2 FOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.3.3 WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.4 Anwendungen in der Praxis . . . . . . . . . . . . . . . . . . . . .
2.4.1 SELECT-Statement, das eine Zeile liefert (single-row) . . .
2.4.2 SELECT-Statement, das mehrere Zeilen liefert (multi-row)
2.4.3 Spalten mehrerer Tabellen ausgeben . . . . . . . . . . . .
2.5 CURSOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.6 Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.6.1 Per-Row-Trigger . . . . . . . . . . . . . . . . . . . . . . .
2.6.2 Per-Statement-Trigger . . . . . . . . . . . . . . . . . . . .
1
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2
2
2
3
3
3
3
4
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
6
6
6
6
8
9
10
10
11
11
11
12
13
14
15
17
18
18
PostgreSQL
E3A
1 Funktionen mit SQL
Mit user defined functions können SQL-Befehle gebündelt und auf einmal abgesetzt werden. Die
einzelnen SQL-Befehle werden mit $$ gekapselt (es darf auch $BODY$ verwendet wrden). Kontrollstrukturen, wie if-elseif-else, sind in SQL nicht möglich, dazu müsste dann PL/pgSQL
verwendet werden.
1.1 ohne Rückgabewert
test=# create or replace function func_a (int) returns void as $$
insert into a values($1);
$$ language sql;
CREATE FUNCTION
test=# select func_a(500);
func_a
-------(1 Zeile)
test=#
Diese Funktion fügt nur einen Wert in eine Tabelle ein, gibt aber sonst nichts aus (liefert kein
sichtbares Ergebnis). void steht sozusagen für stumm – ohne Rückgabewert.
1.2 mit Rückgabewert
test=# create or replace function func_a (int) returns int as $$
insert into a values($1);
select aid from a where aid = $1;
$$ language sql;
CREATE FUNCTION
test=# select func_a(400);
func_a
-------400
(1 Zeile)
Diese Funktion fügt wiederum einen Wert ein, jedoch erfolgt hier eine Ausgabe (400).
Der Aufruf erfolgt immer mit dem Befehl select, dem der Funktionsname übergeben wird.
Beiden Funktionen wird ein Parameter (hier int) übergeben, den das Programm beim Aufruf
verarbeitet.
Eingangs wurde erwähnt, dass es keine Kontrollstrukturen gibt. Allerdings kann in einem selectStatement case-when verwendet werden:
test=# create or replace function
select case
when $1 > $2 then (select 'zahl 1
when $2 > $1 then (select 'zahl 2
when $1 = $2 then (select 'zahl 1
end
$$ language sql;
CREATE FUNCTION
test=# select grkl(4,3);
grkl
----------zahl 1 gr
grkl(int, int) returns text as $$
gr'::text)
gr'::text)
und 2 gleich'::text)
Dieses Programm nimmt zwei Zahlen auf, vergleicht sie und gibt als Ergebnis aus, welche von
beiden größer ist (returns text).
2
PostgreSQL
E3A
1.3 mehrere Parameter
Es können einem Programm (Funktion) auch mehrere Parameter übergeben werden.
test=# create function add_zahl(int, int) returns int as $BODY$
select $1 + $2;
$BODY$ language sql;
CREATE FUNCTION
test=# select add_zahl(5,4);
add_zahl
---------9
(1 Zeile)
Hier werden 2 Angaben gemacht, daher 2 int; diese Zahlen werden vom Programm addiert.
Die Rückgabewerte können von beliebigen Datentypen sein (int, text,...).
1.4 Rückgabewert Tabelle
Ein Rückgabewert kann aber auch vom Typ Tabelle sein:
test=# create function autor_lesen3() returns autor as $$
select * from autor;
$$ language sql;
test=# select autor_lesen3();
autor_lesen3
-------------(2,wenk)
(1 Zeile)
Bei dieser Variante wird hinter dem Wort returns der Name der Tabelle (hier autor) geschrieben, so braucht man sich bei der Ausgabe nicht zu kümmern, welche Spalten ausgegeben werden
sollen, bzw. welche Spalten die Tabelle besitzt.
Beachte: Alle bisherigen Beispiele liefern nur eine Zeile als Ergebnis
1.5 mehrzeiliges Ergebnis
1.5.1 setof
Beachte die beiden Aufrufvarianten – einmal beim select und einmal beim from.
test=# create function sel_buch() returns setof buch as $$
select * from buch;
$$ language sql;
test=# select sel_buch();
sel_buch
---------------------(4,sql,,2008,50.00)
(5,sql,2,2010,50.00)
(2,sql,2,2000,50.00)
(3,sql,1,1998,50.00)
(1,sql,1,1997,10.00)
(5 Zeilen)
test=# select * from sel_buch();
buchnr | buchtitel | verlagid | year | preis
--------+-----------+----------+------+------4 | sql
|
| 2008 | 50.00
5 | sql
|
2 | 2010 | 50.00
2 | sql
|
2 | 2000 | 50.00
3 | sql
|
1 | 1998 | 50.00
1 | sql
|
1 | 1997 | 10.00
(5 Zeilen)
3
PostgreSQL
E3A
1.5.2 setof record
Hier gibt es einen Fallstrick:
test=# create function autor_lesen_setofrec()
returns setof record as $$
select * from autor;
$$ language sql;
CREATE FUNCTION
test=# select * from autor_lesen_setofrec();
FEHLER: eine Spaltendefinitionsliste ist erforderlich ...
ZEILE 1: select * from autor_lesen_setofrec();
^
test=# select autor_lesen_setofrec();
autor_lesen_setofrec
---------------------(2,wenk)
(1,pfeiffer)
(2 Zeilen)
Hier funktioniert der Aufruf beim from nicht, sondern nur beim select. Um das Problem zu
umgehen, muss die Funktion umgeschrieben werden.
test=# create function autor_lesen_setofrec_korr
(out int, out text) returns setof record as $$
select * from autor;
$$ language sql;
CREATE FUNCTION
test=# select autor_lesen_setofrec_korr();
autor_lesen_setofrec_korr
--------------------------(2,wenk)
(1,pfeiffer)
(2 Zeilen)
test=# select * from autor_lesen_setofrec_korr();
column1 | column2
---------+---------2 | wenk
1 | pfeiffer
(2 Zeilen)
Jetzt kann die Funktion in beiden Teilen aufgerufen werden.Die Funktion liefert zwei Werte
zurück (daher zwei OUT-Angaben), weil das select-Kommando zwei Spalten liefert. Bei diesen OUT-Angaben kann auch neben dem Datentyp ein beliebiger Name angegeben werden, der
wiederum in einer weiteren Abfrage verwendet wird:
test=# create function autor_lesen_setofrec_korr1
(out nr int, out name text)
returns setof record as $$
select * from autor;
$$ language sql;
CREATE FUNCTION
test=# select * from autor_lesen_setofrec_korr1 ();
nr | name
----+---------2 | wenk
1 | pfeiffer
(2 Zeilen)
test=# select * from autor_lesen_setofrec_korr1 () where nr =1;
nr | name
----+---------1 | pfeiffer
(1 Zeile)
4
PostgreSQL
E3A
Übungen:
1. Schreibe eine Funktion, die die Kunden und ihre Bestellungen ausgibt. Das Ergebnis soll
in etwa so aussehen, wobei ein Wert als Parameter angegeben werden kann.
bestellungen=# select kd_best(108);
kd_best
--------------------------(108,Wendtland,Hans,1011)
(108,Wendtland,Hans,1012)
(108,Wendtland,Hans,1013)
(3 Zeilen)
bestellungen=# select * from kd_best(108);
kundennr | name
| vorname | bestellnr
----------+-----------+---------+----------108 | Wendtland | Hans |
1011
108 | Wendtland | Hans |
1012
108 | Wendtland | Hans |
1013
(3 Zeilen)
2. Schreibe eine Funktion, die als Parameter 2 Zahlen und eine Rechenoperation (+ − ∗/)
aufnimmt und die beiden Zahlen entsprechend verarbeitet.
test=# select rechnen(2,3,'*');
test=# select rechnen(2,3,'+');
5
PostgreSQL
E3A
2 Funktionen mit PL/pgSQL
2.1 Aufbau einer Funktion
2.1.1 Funktionscode
Der Code wird zwischen BEGIN und END eingeschlossen, am Ende wird language plpgsql angegeben1 .
2.1.2 Variablen
Mögliche Definitionen von Variablen sehen so aus:
Definition
Bedeutung
userid integer
Datentyp Dezimalzahl
vorname text
Datentyp Text
kundenrow kunden%ROWTYPE;
zusammengesetzte Variable vom Typ wie
Tabelle kunden
kundenspalte kunden.vorname%TYPE ;
Variable vom gleichen Typ wie Spalte vorname in Tabelle kunden; Vorteil: ändert
sich in der Spalte der Datentyp (alter),
dann muss der Variablentyp nicht geändert werden
arow RECORD;
ähnlich ROWTYPE, nur ohne Struktur
zahl1 alias for $1
für Parameter kann ein Alias vergeben
werden, vor allem dann, wenn viele Parameter übergeben werden (übersichtlicher
als $1, $2, $3, etc.)
Tabelle 1: Variablen Datentypen
Ein einfaches Beispiel, das zwei Zahlen dividiert
test=# create function division_simple (float, float) returns float as $$
declare
/* hier werden Variablen deklariert */
zahl1 alias for $1;
/* Variablen (Aliase) für die Parameter */
zahl2 alias for $2;
begin
return zahl1/zahl2; /* gibt Ergebnis zurück */
end $$ language plpgsql;
Diese Funktion nimmt zwei Parameter (typ float) auf und gibt ein Ergebnis vom Typ float
zurück. Im declare-Block werden Variablen definiert (alias – zum Umschreiben – kann dann
verwendet werden, wenn viele Variablen übergeben werden und man nachher nicht mehr weiß,
wofür steht denn eigentlich $1, $2 etc. )
Im Block mit begin - end wird der eigentliche Programmtext eingefügt, hier nur ein einfaches
return.
Im nächsten Beispiel wird zusätzlich eine Variable deklariert
test=# create function f_gross(text) returns text as $$
declare
wert1 alias for $1;
1
sollte plpgsql nicht vorhanden sein, wird sie mit dem Befehl »create language plpgsql« nachinstalliert
6
PostgreSQL
E3A
wert_gross text;
/* Hilfsvariable */
begin
wert_gross := UPPER(wert1); /* Variable wert1 in wert_gross umspeichern */
return wert_gross;
end $$ language plpgsql;
CREATE FUNCTION
test=# select f_gross('hallo');
f_gross
--------HALLO
(1 Zeile)
Dieses Programm nimmt einen Parameter auf (Typ text) und gibt ein Ergebnis vom Typ text
zurück. Zusätzlich wird im declare-Block eine weitere Variable wert_gross deklariert, die im
eigentlichen Programmblock mittels UPPPER umgewandelt wird. Einer Variable wird mittels :=
ein Wert zugewiesen.
Jedes Wort, das einem Variablennamen entspricht wird interpretiert. Daher funktioniert folgendes Programm nicht:
test=# create function f_artikel1(id int) returns text as $$
declare
art alias for $1;
bez text;
begin
/* hier wird bez aus der Tabelle selektiert (select bez from ...),
bez ist aber eine Variable vom Typ text, die noch nicht belegt ist (also leer ist),
daher wird ein nicht-vorhandener Wert in die Variable bez gelegt,
daher wird auch nichts zurückgegeben
*/
select bez from artikel into bez where aid = art;
return bez;
end $$ language plpgsql;
CREATE FUNCTION
test=# select f_artikel1(1);
f_artikel1
-----------(1 Zeile)
Folgendes Programm aber schon:
test=# create function f_artikel(id int) returns text as $$
declare
art alias for $1;
bez text;
begin
/* hier wird direkt mit a.bez angedeutet, dass damit eine Spalte der Tabelle a (artikel) gemeint ist
es wird ein Wert in die Variable bez gelegt, der auch mit return zurückgegeben werden kann
*/
select a.bez from artikel a into bez where aid = art;
return bez;
end $$ language plpgsql;
CREATE FUNCTION
test=# select f_artikel(1);
f_artikel
----------maus
(1 Zeile)
7
PostgreSQL
E3A
2.1.3 Fehler abfragen
Noch mals zurück zum Beispiel mit der Division. Hier sollte ja bei einem Versuch, durch 0 zu
dividieren, ein Hinweis aufscheinen, dass dies nicht erlaubt ist.
test=# create or replace function division( numeric,numeric ) returns numeric as $$
declare
zahl1 alias for $1 ;
zahl2 alias for $2 ;
begin
if zahl2 = 0 then
raise exception 'division durch 0 nicht definiert'; /* im Falle eines Fehlers */
else
return zahl1/zahl2;
end if;
end $$ language plpgsql;
Hier wird, wenn die zweite Zahl 0 beträgt, ein Fehler (exception) ausgegeben und das Programm
bricht ab, ansonsten wird zahl1 durch zahl2 dividiert. Die Ausgabe sieht so aus.
test=# select division(4,0);
FEHLER: division durch 0 nicht definiert
test=#
Letzteres Beispiel lässt sich auch so ausprogrammieren:
test=# create function division1 (numeric, numeric) returns numeric as $$
declare
zahl1 alias for $1;
zahl2 alias for $2;
begin
return $1/$2;
exception when division_by_zero then /* division_by_zero ist eine Systemmeldung */
raise notice 'division durch 0 nicht definiert';
return null;
end $$ language plpgsql;
PostgreSQL kann je nach Ereignis unterschiedliche Fehler, Meldungen und Warnungen ausgeben.
Hier wird auf den Fehler vom Typ division_by_zero reagiert, tritt dieser auf, dann wird ein
Hinweis (notice) ausgegeben, dass »division durch 0 nicht definiert« ist. Die möglichen
Codes sind auf der Webseite2 einzusehen. Die Ausgabe für das zweite Programm sieht so aus.
test=# select division1(4,0);
HINWEIS: division durch 0 nicht definiert
division1
----------(1 Zeile)
test=#
Da eine Funktion einen return-Wert benötigt, muss hier in dem zweiten Beispiel zumindest ein
return null angegeben werden, ansonsten kommt es zu einer unerwünschten Fehlermeldung.
Im ersten Programm bricht ja die raise exception ab.
2
http://www.postgresql.org/docs/8.4/static/errcodes-appendix.html
8
PostgreSQL
E3A
2.2 Vergleiche
Vergleiche in einer plpgsql-Funktion werden mit IF realisiert.
test=# create function zahl_vergleich(integer, integer) returns text as $$
declare
zahl1 alias for $1;
zahl2 alias for $2;
ausgabe text;
begin
if zahl1 < zahl2 then
ausgabe := 'zahl 2 groesser';
elseif zahl2 < zahl1 then
ausgabe := 'zahl 1 groesser';
else
ausgabe :='zahlen gleich';
end if;return ausgabe;
end $$ language plpgsql;
CREATE FUNCTION
test=#
Hier wird für die Ausgabe eine Variable namens ausgabe definiert, je nach dem, welche Zahl
größer ist, wird diese Variable mit einem anderen Wert (Datentyp text) belegt und mittels
return ausgegeben.
In einer Funktion kann statt return auch mit raise notice gearbeitet werden, der Rückgabewert der Funtkion ist dann void.
test=# create function division_void( numeric,numeric ) returns void as $$
declare
zahl1 alias for $1 ;
zahl2 alias for $2 ;
result numeric;
/* Variable für das Ergebnis */
begin
if zahl2 = 0 then
raise exception 'division durch 0 nicht definiert';
else
result := zahl1 / zahl2; /* Ergebnis in result speichern */
raise notice '%',result; /* Hinweis ausgeben */
end if;
end $$ language plpgsql;
CREATE FUNCTION
test=# select division_void(4,2);
HINWEIS: 2.0000000000000000
division_void
--------------(1 Zeile)
test=# select division_void(4,0);
FEHLER: division durch 0 nicht definiert
test=# select division_void(4.2,2.1);
HINWEIS: 2.0000000000000000
division_void
--------------(1 Zeile)
Bei einem regulären Ergebnis wird eine raise notice – also ein Hinweis ausgegeben, bei einer
Division durch 0 eine raise exception; die Funktion gibt void (also nichts) zurück.
9
PostgreSQL
E3A
2.3 Schleifen
2.3.1 LOOP
test=# create or replace function
declare zahl int;
begin
zahl := 1;
loop
zahl := zahl+1;
/*
exit when zahl > 5;
/*
end loop;
/*
return zahl;
end; $$ language plpgsql;
CREATE FUNCTION
test=# select schleife_loop();
schleife_loop
--------------6
(1 Zeile)
schleife_loop() returns integer as $$
if zahl > 5 then */
exit;
*/
end if;
*/
Die Schleife erhöht die Variable zahl ständig um 1 und bricht ab, wenn sie einen Wert > 5
erreicht. Das IF im Kommentar arbeitet gleich wie EXIT WHEN. Sollen alle Zahlen von 1 − 5
ausgegeben werden, so muss die Funktion umgeschreiben werden:
test=# create or replace function schleife_loop2() returns integer as $$
declare zahl int;
begin
zahl := 1;
loop
raise notice 'zahl ist %',zahl; /* % steht für die Variable 'zahl' */
zahl := zahl + 1;
if zahl > 5 then
return null;
exit;
end if;
end loop;
end; $$ language plpgsql;
CREATE FUNCTION
test=# select schleife_loop2();
HINWEIS: zahl ist 1
HINWEIS: zahl ist 2
HINWEIS: zahl ist 3
HINWEIS: zahl ist 4
HINWEIS: zahl ist 5
schleife_loop2
---------------(1 Zeile)
Will man bei obiger Schleife statt RAISE NOTICE ein RETURN verwenden, so muss die Schleife so
umgebaut werden:
test=# create function schleife_loop3() returns setof integer AS $$
declare
zahl int;
begin
zahl := 1;
loop
return query select zahl;
zahl :=zahl+1;
if zahl > 5 then exit; end if;
end loop;
end; $$ language plpgsql
10
PostgreSQL
E3A
2.3.2 FOR
Eine FOR-Schleife läuft innerhalb eines fixen Bereichs (Stichwort Zähler)
test=# create function schleife_for() returns integer as $$
declare zahl int;
begin
zahl := 1;
for zahl in 1..5 loop
/* 1 bis 5 */
raise notice '%',zahl;
end loop;
return null;
end; $$ language plpgsql;
CREATE FUNCTION
test=# select schleife_for();
HINWEIS: 1
HINWEIS: 2
HINWEIS: 3
HINWEIS: 4
HINWEIS: 5
schleife_for
-------------(1 Zeile)
2.3.3 WHILE
test=# create or replace function schleife_while() returns void as $$
declare
zaehler int := 1;
/* Startwert */
begin
WHILE zaehler < 5 LOOP
/* solange zaehler kleiner 5 ist ..*/
raise notice 'zahl ist %', zaehler; /* gib das aus */
zaehler := zaehler + 1; /* erhöhe nachher zaehler um 1 */
END LOOP;
END $$ language plpgsql;
CREATE FUNCTION
test=# select schleife_while();
HINWEIS: zahl ist 1
HINWEIS: zahl ist 2
HINWEIS: zahl ist 3
HINWEIS: zahl ist 4
schleife_while
---------------(1 Zeile)
2.4 Anwendungen in der Praxis
Diese Beispiele waren bisher nicht sehr praxisrelevant, daher im Folgenden einige Beispiele, in
denen PL/pgSQL-Funktionen angewendet werden. In einer Funktion können alle Befehle, die
kein result set zurückliefern, wie gewohnt aufgerufen werden (INSERT, UPDATE, DELETE),
aber kein SELECT (ausser mit INTO).
test=# select * from artikel;
aid |
bez
| preis | typ
-----+--------------+--------+----1 | maus
| 15.99 | 1
2 | laptop
| 400.00 | 1
3 | laserdrucker | 150.99 | 1
4 | tastatur
| 39.99 | 1
5 | virenschutz | 200.00 | 2
(5 Zeilen)
test=# create function f_updartikel(artikeltext text, artikelnummer int) returns void as $$
declare
art alias for $1;
artnr alias for $2;
begin
11
PostgreSQL
E3A
update artikel set bez = art where aid = artnr;
end $$ language plpgsql;
CREATE FUNCTION
test=# select f_updartikel('ratte', 1);
f_updartikel
-------------(1 Zeile)
test=# select * from artikel;
aid |
bez
| preis | typ
-----+--------------+--------+----2 | laptop
| 400.00 | 1
3 | laserdrucker | 150.99 | 1
4 | tastatur
| 39.99 | 1
5 | virenschutz | 200.00 | 2
1 | ratte
| 15.99 | 1
(5 Zeilen)
2.4.1 SELECT-Statement, das eine Zeile liefert (single-row)
Hier wird INTO verwendet (funktioniert prinzipiell mit SELECT, UPDATE, DELETE, INSERT)
test=# create function f_artikel(id int) returns text as $$
declare
art alias for $1;
bez text;
begin
select a.bez from artikel a into bez where aid = art; /* liefert einzeiliges Ergebnis */
return bez;
end $$ language plpgsql;
CREATE FUNCTION
test=# select f_artikel(1);
f_artikel
----------maus
(1 Zeile)
Das RETURN kann auch weggelassen werden (oder als leeres »RETURN« stehen, dafür muss als
Parameter mindestens ein OUT definiert werden.
test=# create function f_artikel(int, out bez text) as $$
declare
artikelnr alias for $1;
begin
select a.bez into bez from artikel a where aid = artikelnr;
return;
/* kann auch wegbleiben */
end $$ language plpgsql;
CREATE FUNCTION
test=# select f_artikel(1);
f_artikel
----------ratte
(1 Zeile)
Ein anderes Beispiel, in dem OUT-Parameter verwendet werden.
bestellungen=# CREATE FUNCTION f_getkundennr_mitout (text,text, out kunde int) returns integer AS $$
DECLARE
l_name ALIAS FOR $1;
f_name ALIAS FOR $2;
BEGIN
SELECT INTO kunde kundennr FROM kunden
WHERE name = l_name AND vorname = f_name;
END;
$$ LANGUAGE plpgsql;
12
PostgreSQL
E3A
2.4.2 SELECT-Statement, das mehrere Zeilen liefert (multi-row)
Hier sind Schleifen, Cursor oder ein RETURN QUERY notwendig.
Ein Beispiel mit einer FOR-Schleife:
bestellungen=# create function f_kunden() returns setof kunden as $$
declare
erg kunden%rowtype; /* Variable erg hat alle Spalten der Tabelle kunden */
begin
for erg in select * from kunden
loop
return next erg;
end loop;
end $$ language plpgsql;
CREATE FUNCTION
Das gleiche Beispiel mit RETURN QUERY:
bestellungen=# create function f_kunden_ohne_loop() returns setof kunden as $$
declare
erg kunden%rowtype; /* Variable erg hat alle Spalten der Tabelle kunden */
begin
/*for erg in select * from kunden
loop
return next erg;
end loop;*/
return query select * from kunden;
end $$ language plpgsql;
Ein Beispiel für eine Funktion mit IN-Parameter, die mehrere Zeilen ausliest (Achtung: der
IN-Parameter darf nicht gleich lauten wie die verwendete Spalte !!!)
test=# create function f_bestschleife(in k int) returns setof bestellungen as $$
declare
erg bestellungen%ROWTYPE;
begin
for erg in select * from bestellungen where kid = $1
loop
return next erg;
end loop;
end $$ language plpgsql;
CREATE FUNCTION
test=# select * from bestellungen where kid = 1;
bid |
bdat
| kid
-----+------------+----1 | 2010-08-07 | 1
2 | 2010-08-07 | 1
Ebenso muss man aufpassen, wenn IN-Parameter gleich lauten, wie eine Spalte. Hier kommt es
nicht zum gewünschten Ergebnis:
test=# create function f_bestschleife(in k int) returns setof bestellungen as $$
declare
erg bestellungen%ROWTYPE;
kid alias for $1; /* Variable heisst gleich wie Spalte */
begin
for erg in select * from bestellungen where kid = kid
loop
return next erg;
end loop;
end $$ language plpgsql;
CREATE FUNCTION
test=# select * from f_bestschleife(2);
bid |
bdat
| kid
-----+------------+----1 | 2010-08-07 | 1
2 | 2010-08-07 | 1
3 | 2010-08-07 | 2
(3 Zeilen)
13
PostgreSQL
E3A
Hingegen stimmt hier das Ergebnis:
test=# create function f_bestschleife(in k int) returns setof bestellungen as $$
declare
erg bestellungen%ROWTYPE;
ki alias for $1; /* Variable heisst anders als Spalte */
begin
for erg in select * from bestellungen where kid = ki
loop
return next erg;
end loop;
end $$ language plpgsql;
CREATE FUNCTION
test=# select * from f_bestschleife(2);
bid |
bdat
| kid
-----+------------+----3 | 2010-08-07 | 2
(1 Zeile)
Bei Verwendung mehrerer Tabellen genügt ein einfaches rowtype nicht mehr, daher muss ein
eigener Typ kreiert werden, der die Spalten aus den jeweiligen Tabellen beinhaltet. Dieser Typ
kann dann im returns setof eingebaut werden.
2.4.3 Spalten mehrerer Tabellen ausgeben
bestellungen=# create type t_kdbest as (kundennr int, name text, vorname text, bestellnr int);
CREATE TYPE
bestellungen=# create function f_kdbest1(in kundennummer int) returns setof t_kdbest as $$
declare
kdnr alias for $1;
erg t_kdbest%rowtype;
begin
for erg in
/* für alle Zeilen des Ergebnisses ...*/
select kunden.kundennr, name, vorname , bestellnr
from kunden, bestellung where
kunden.kundennr = bestellung.kundennr and kunden.kundennr = kdnr
loop
return next erg;
/* gib in einer Schleife alle Zeilen der Reihe nach aus */
end loop;
end $$ language plpgsql;
CREATE FUNCTION
bestellungen=# select f_kdbest1(108);
f_kdbest1
--------------------------(108,Wendtland,Hans,1011)
(108,Wendtland,Hans,1012)
(108,Wendtland,Hans,1013)
(3 Zeilen)
Wird returns table verwendet, dann dürfen die Spalten nicht gleich lauten, wie die tatsächlichen Spaltennnamen in der Tabelle. Die ersten beiden Abfragen funktionieren, die dritte nicht:
bestellungen=# create function f_arttab(artnr int) returns table
(a character varying(64), b character varying(64)) as $$
declare
artnum alias for $1;
begin
return query select titel, untertitel from artikel where artikelnr = artnum;
end $$ language plpgsql;
CREATE FUNCTION
auch ok
test=# create or replace function f_autor_a(anr int) returns table (nr int, nm text) as $$
declare
autornr alias for $1;
begin
return query select anr, name from autor where anr = autornr;
end $$ language plpgsql;
CREATE FUNCTION
14
PostgreSQL
E3A
nicht ok
test=# create or replace function f_autor_a(anr int) returns table (anr int, name text) as $$
declare
autornr alias for $1;
begin
return query select anr, name from autor where anr = autornr;
end $$ language plpgsql;
CREATE FUNCTION
2.5 CURSOR
Cursor können eingesetzt werden, um große Datenmengen ’durchzuscrollen’; die Daten müssen
nicht komplett in den Speicher geladen werden, sondern können Zeile für Zeile durchgegangen
werden. Zusätzlich kann man mit einem Cursor im Ergebnis vor- und zurücknavigieren.
Es gibt zwei Cursor-Arten:
• Gebundene Cursor: Sind an eine Abfrage gebunden und werden im DECLARE-Block beispielsweise mit
crs CURSOR FOR select * FROM artikel; --der Cursor hat den namen 'crs'
definiert. Diese Cursor werden im Programmblock mit
OPEN crs;
geöffnet.
• Ungebundene Cursor: Diese werden so deklariert:
crs refcursor;
und anschließend im Programmblock mit
OPEN crs FOR SELECT * FROM artikel;
geöffnet.
Mit FETCH wird eine Ergebniszeile geliefert, diese muss in eine Variable vom Typ ROW oder RECORD
gespeichert werden. Mit MOVE kann auch im Ergebnis ’gescrollt’ werden, allerdings liefert diese
Funktion kein Ergebnis.
Ein Beispiel an folgender Tabelle artikel:
test=# select * from artikel;
aid |
bez
| preis
| typ
-----+--------------+--------+----2 | laptop
| 400.00 | 1
3 | laserdrucker | 150.99 | 1
4 | tastatur
| 39.99 | 1
5 | virenschutz | 200.00 | 2
1 | ratte
| 15.99 | 1
test=# create or replace function f_crs(integer) returns record as $$
declare
crs refcursor;
crs_geb cursor for select * from artikel order by aid;-- an ein query gebunden
erg record; --typ record zum abspeichern
begin
for erg in select * from artikel order by aid
loop
raise info 'tab. artikel: %', erg;--record ausgeben
end loop;
open crs for select * from artikel order by aid;--refcursor
fetch crs into erg; --zeile in erg speichern
raise info 'ergebnis: %', erg;
fetch last from crs into erg; --letzte zeile holen
raise info 'letzte zeile: %',erg;
15
PostgreSQL
E3A
fetch relative -3 from crs into erg; --3 zeilen vorher holen
raise info 'erg 3 vor letzten zeile: %', erg;
close crs;
open crs_geb; --gebundenen cursor öffnen
fetch crs_geb into erg; --in variable speichern
raise info 'ergebnis gebundener cursor: %', erg;
fetch relative +2 from crs_geb into erg;--2 zeilen nach vor
raise info 'ergebnis gebundener cursor 2 zeilen nachher: %', erg;
close crs_geb; --schließen
return null;
end $$ language plpgsql;
Das Ergebnis:
test=# select f_crs(1);
INFO: tab. artikel: (1,ratte,15.99,1)
INFO: tab. artikel: (2,laptop,400.00,1)
INFO: tab. artikel: (3,laserdrucker,150.99,1)
INFO: tab. artikel: (4,tastatur,39.99,1)
INFO: tab. artikel: (5,virenschutz,200.00,2)
INFO: ergebnis: (1,ratte,15.99,1)
INFO: letzte zeile: (5,virenschutz,200.00,2)
INFO: erg 3 vor letzten zeile: (2,laptop,400.00,1)
INFO: ergebnis gebundener cursor: (1,ratte,15.99,1)
INFO: ergebnis gebundener cursor 2 zeilen nachher: (3,laserdrucker,150.99,1)
f_crs
------(1 Zeile)
Ein Cursor kann auch als Referenz genutzt werden; dies ist nur in einer Transaktion möglich. In
der Funktion müssen die Parameter dann immer refcursor lauten, ebenso müssen die Rückgabewerte returns refcursor lauten.
test=# create or replace function f_crs1(refcursor) returns refcursor as $$
declare
curs_ref alias for $1;
begin
open curs_ref for select * from person;
return curs_ref;
end $$ language plpgsql;
CREATE FUNCTION
test=# begin; --Beginn der Transaktion
BEGIN
test=# select f_crs1('a'); --refcursor als Parameter
f_crs1
-------a
(1 Zeile)
test=# fetch next from a; --1. zeile in a laden
pid | name | titel | aid
-----+------+-------+----1 | hans | Herr |
(1 Zeile)
test=# fetch next from a; --nächste zeile in a laden
pid | name | titel | aid
-----+------+-------+----2 | sepp |
|
(1 Zeile)
test=# fetch prior from a; --vorige zeile in a laden
pid | name | titel | aid
-----+------+-------+----1 | hans | Herr |
(1 Zeile)
test=# commit; --transaktion abschließen (bestätigen)
COMMIT
test=#
16
PostgreSQL
E3A
Ein Cursor kann auch mit Parametern verwendet werden:
test=# create function f_crs_para(integer) returns setof person as $$
declare
crs cursor (ID integer) for select * from person where aid = ID; --cursor mit parameter
prs person%ROWTYPE; --ganze zeile in einem block
nr alias for $1;
begin
open crs(nr); -öffnen mit Parameter
loop
fetch crs into prs; --in variable laden
exit when not found; --nach letzter zeile aussteigen
return next prs; --zurückgeben
end loop;
close crs;
end $$ language plpgsql;
Vergleich zwischen Cursor mit FOR und Cursor mit WHILE
• FOR
bestellungen=# CREATE FUNCTION titel_lesen_for(int) RETURNS SETOF TEXT AS $$
DECLARE
erg RECORD;
bez artikel.titel%TYPE;
prs alias for $1;
BEGIN
FOR erg IN SELECT titel, artikelpreis FROM artikel
WHERE artikelpreis < prs
LOOP
--implizites OPEN und FETCH
bez := erg.titel||' '||erg.artikelpreis;
RETURN NEXT bez;
END LOOP; --implizites CLOSE
RETURN;
END
$$ LANGUAGE 'plpgsql';
• WHILE
bestellungen=# CREATE FUNCTION titel_lesen_while(integer) RETURNS SETOF TEXT AS $$
DECLARE
prs alias for $1;
erg CURSOR FOR
SELECT titel ||' '|| artikelpreis
FROM artikel
WHERE artikelpreis < prs;
bez artikel.titel%TYPE;
BEGIN
OPEN erg;
FETCH erg INTO bez;
WHILE FOUND
LOOP
RETURN NEXT bez;
FETCH erg INTO bez;
END LOOP;
RETURN;
END
$$ LANGUAGE 'plpgsql';
2.6 Trigger
Trigger ermöglichen es, eine bestimmte Aktion auszuführen, wenn ein bestimmtes Ereignis eintritt. Trigger sind ähnlich den Rules mit dem Unterschied, dass ein Trigger in PostgreSQL
immer eine Funktion aufruft, deren RETURN-Wert vom Typ TRIGGER ist.
• Das auslösende Ereignis kann ein INSERT, UPDATE, DELETE sein
• Trigger können pro Zeile (per-row-Trigger oder per-statement ausgeführt werden
• Trigger können vor oder nach dem Ereignis ’gefeuert’ werden
17
PostgreSQL
E3A
2.6.1 Per-Row-Trigger
Zuerst muss die Funktion erstellt werden, danach der Trigger, der die Funktion aufruft. Ein
Beispiel: In der Tabelle bestellungen soll für neue Werte nur ein Datum ≥ das heutige erlaubt
sein.
test=# select * from bestellungen;
bid |
bdat
| kid
-----+------------+----1 | 2010-08-07 | 1
2 | 2010-08-07 | 1
3 | 2010-08-07 | 2
4 | 2010-11-25 | 2
test=# create function f_check_bestdat() returns trigger as $$
begin
if (new.bdat) < now() then
--wenn neues Datum kleiner heutiges ist
new.bdat := now();
--auf heutiges setzen
end if;
return new;
end;
$$ language plpgsql;
test=# create trigger trig_checkbdat before insert or update on bestellungen
for each row
execute procedure f_check_bestdat();
Der Trigger muss klarerweise vor – before dem Einfügen/Aktualisieren aktiv werden, nahcher
wäre es ja zu spät. Für jede zu ändernde/einzufügende Zeile wird die Funktion aufgerufen (for
each row). Ein INSERT mit einem Datum vor heute (26. November 2010) führt zu folgendem
Ergebnis:
test=# insert into bestellungen values(5, '2010-10-24',2);
INSERT 0 1
test=# select * from bestellungen;
bid |
bdat
| kid
-----+------------+----1 | 2010-08-07 | 1
2 | 2010-08-07 | 1
3 | 2010-08-07 | 2
4 | 2010-11-25 | 2
5 | 2010-11-25 | 2
(5 Zeilen)
Aktionen, die nach dem Einfügen stattfinden, könnten zum Beispiel Einträge in Log-Tabellen
sein. NEW wird verwendet beim Einfügen von neuen oder Ändern mit enuen Werten, OLD wird
verwendet, wenn bestehende Daten angesprochen werden.
2.6.2 Per-Statement-Trigger
Diese werden zum Beispiel verwendet, wenn eine Tabelle gelöscht wird und man will diesen
Vorgang protokollieren. So braucht nicht für jede gelöschte Zeile ein Trigger gestartet zu werden,
sondern nur einmal. Ein Beispiel:
test=# select * from test2;
id
---1
2
3
(3 Zeilen)
test=# create function f_deltest2() returns trigger as $$
begin
insert into test2_log values(now(), session_user);
return null;
end $$ language plpgsql;
test=# create trigger trig_deltest2 after delete on test2 for each statement execute procedure f_deltest2();
18
PostgreSQL
E3A
Übungen:
• Erstelle folgende Tabellen (FK – PK-Verbindung über kid):
test=# select * from kunden;
kid | name | anzahl_best
-----+--------+------------1 | meier |
0
3 | dorfer |
0
2 | huber |
0
(3 Zeilen)
test=# select * from bestellungen;
bid |
bdat
| kid
-----+------------+----1 | 2010-08-07 | 1
2 | 2010-08-07 | 1
3 | 2010-08-07 | 2
4 | 2010-11-25 | 2
5 | 2010-11-25 | 2
Schreibe eine Triggerfunktion, die nach einem INSERT in die Tabelle bestellungen den
Wert der Spalte anzahl_best des entsprechenden Kunden (Tabelle kunden) um 1 erhöht.
test=# insert into bestellungen values(7, '2010-11-25',2); --kid 2 ist huber
INSERT 0 1
test=# select * from kunden;
kid | name | anzahl_best
-----+--------+------------1 | meier |
0
3 | dorfer |
0
2 | huber |
1
(3 Zeilen)
• Erstelle eine Tabelle ma
test=# CREATE TABLE ma (
name text,
gehalt integer,
datum_aenderung timestamp,
user_aenderung text
);
Schreibe dazu eine Triggerfunktion, die bei einem INSERT oder UPDATE auf folgende Fehler
prüft:
– ist der Name leer (NULL – sprich: [n2l])
– ist das Gehalt leer (NULL – sprich: [n2l])
– ist das Gehalt ≤ 0
Wenn ja, dann sollen entsprechende Fehlermeldungen ausgegeben werden.
test=# insert into ma (name) values('meier');
FEHLER: meier kann kein leeres gehalt haben
test=# insert into ma (name,gehalt) values('meier',-2);
FEHLER: meier kann kein negatives gehalt haben
test=# insert into ma (gehalt) values(2000);
FEHLER: name leer ist verboten
Bei fehlerlosen Daten soll in die Spalte datum_aenderung der aktuelle Zeitstempel (now())
und der ausführende Benutzer (current_user) in die Spalte user_aenderung eingetragen
werden.
--INSERT
test=# insert into ma (name,gehalt) values('huber',2000);
INSERT 0 1
test=# select * from ma;
name | gehalt |
datum_aenderung
| user_aenderung
-------+--------+----------------------------+---------------huber | 2000 | 2010-11-25 21:38:33.740366 | postgres
--UPDATE
test=# update ma set gehalt = 3000 where name = 'huber';
UPDATE 1
test=# select * from ma;
19
PostgreSQL
E3A
name | gehalt |
datum_aenderung
| user_aenderung
-------+--------+----------------------------+---------------huber | 3000 | 2010-11-25 22:00:15.376363 | postgres
1. Lösung
test=# CREATE FUNCTION f_trig_kundenbest()
RETURNS trigger AS
$BODY$
begin
update kunden set anzahl_best = anzahl_best + 1 where kid = new.kid;
return null;
end $BODY$
LANGUAGE 'plpgsql';
test=# CREATE TRIGGER trig_kundenbest
AFTER INSERT
ON bestellungen
FOR EACH ROW
EXECUTE PROCEDURE f_trig_kundenbest();
2. Lösung
test=# CREATE FUNCTION ma_check()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.name IS NULL THEN
RAISE EXCEPTION 'name leer ist verboten';
END IF;
IF NEW.gehalt IS NULL THEN
RAISE EXCEPTION '% kann kein leeres gehalt haben', NEW.name;
END IF;
IF NEW.gehalt <= 0 THEN
RAISE EXCEPTION '% kann kein negatives gehalt haben', NEW.name;
END IF;
NEW.datum_aenderung := now();
NEW.user_aenderung := current_user;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
test=# CREATE TRIGGER ma_check_trig
BEFORE INSERT OR UPDATE
ON ma
FOR EACH ROW
EXECUTE PROCEDURE ma_check();
20