E3A PostgreSQL

Funktionen in PostgreSQL

Inhaltsverzeichnis

1 Funktionen mit SQL 21.1 ohne Rückgabewert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21.2 mit Rückgabewert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21.3 mehrere Parameter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.4 Rückgabewert Tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.5 mehrzeiliges Ergebnis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1.5.1 setof . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.5.2 setof record . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

2 Funktionen mit PL/pgSQL 72.1 Aufbau einer Funktion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

2.1.1 Funktionscode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72.1.2 Variablen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72.1.3 Fehler abfragen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

2.2 Vergleiche . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102.3 Schleifen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

2.3.1 LOOP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112.3.2 FOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122.3.3 WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

2.4 Anwendungen in der Praxis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132.4.1 SELECT-Statement, das eine Zeile liefert (single-row) . . . . . . . . . . . . 132.4.2 SELECT-Statement, das mehrere Zeilen liefert (multi-row) . . . . . . . . . 142.4.3 Spalten mehrerer Tabellen ausgeben . . . . . . . . . . . . . . . . . . . . . 16

2.5 CURSOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172.6 Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

2.6.1 Per-Row-Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202.6.2 Per-Statement-Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

1

PostgreSQL E3A

1 Funktionen mit SQL

Mit user defined functions können SQL-Befehle gebündelt und auf einmal abgesetzt werden. Dieeinzelnen SQL-Befehle werden mit $$ gekapselt (es darf auch $BODY$ verwendet wrden). Kon-trollstrukturen, wie if-elseif-else, sind in SQL nicht möglich, dazu müsste dann PL/pgSQLverwendet werden.

1.1 ohne Rückgabewert

create function func_a (int) returns void as $$insert into a values($1);$$ language sql;CREATE FUNCTIONtest=# 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 keinsichtbares Ergebnis). void steht sozusagen für stumm – ohne Rückgabewert.

1.2 mit Rückgabewert

create function func_a (int) returns int as $$insert into a values($1);select aid from a where aid = $1;$$ language sql;CREATE FUNCTIONtest=# 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 Aufrufverarbeitet.Eingangs wurde erwähnt, dass es keine Kontrollstrukturen gibt. Allerdings kann in einem select-Statement case-when verwendet werden:

create function grkl(int, int) returns text as $$select casewhen $1 > $2 then (select 'zahl 1 gr'::text)when $2 > $1 then (select 'zahl 2 gr'::text)when $1 = $2 then (select 'zahl 1 und 2 gleich'::text)end$$ language sql;CREATE FUNCTIONtest=# select grkl(4,3);

grkl−−−−−−−−−−−

zahl 1 gr

2

PostgreSQL E3A

Dieses Programm nimmt zwei Zahlen auf, vergleicht sie und gibt als Ergebnis aus, welche vonbeiden größer ist (returns text).

1.3 mehrere Parameter

Es können einem Programm (Funktion) auch mehrere Parameter übergeben werden.

create function add_zahl(int, int) returns int as $BODY$select $1 + $2;$BODY$ language sql;CREATE FUNCTIONtest=# 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:

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) geschrie-ben, so braucht man sich bei der Ausgabe nicht zu kümmern, welche Spalten ausgegeben werdensollen, 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.

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)

3

PostgreSQL E3A

(5 Zeilen)

test=# select ∗ from sel_buch();buchnr | buchtitel | verlagid | year | preis

−−−−+−−−−−−−+−−−−−+−−−+−−−

4 | sql | | 2008 | 50.005 | sql | 2 | 2010 | 50.002 | sql | 2 | 2000 | 50.003 | sql | 1 | 1998 | 50.001 | sql | 1 | 1997 | 10.00

(5 Zeilen)

1.5.2 setof record

Hier gibt es einen Fallstrick:

create function autor_lesen_setofrec()returns setof record as $$

select ∗ from autor;$$ language sql;CREATE FUNCTIONtest=# 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 zuumgehen, muss die Funktion umgeschrieben werden.

create function autor_lesen_setofrec_korr(out int, out text) returns setof record as $$select ∗ from autor;$$ language sql;CREATE FUNCTIONtest=# 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 | wenk1 | pfeiffer

(2 Zeilen)

Jetzt kann die Funktion in beiden Teilen aufgerufen werden.Die Funktion liefert zwei Wertezurück (daher zwei OUT-Angaben), weil das select-Kommando zwei Spalten liefert. Bei die-sen OUT-Angaben kann auch neben dem Datentyp ein beliebiger Name angegeben werden, derwiederum in einer weiteren Abfrage verwendet wird:

4

PostgreSQL E3A

create function autor_lesen_setofrec_korr1(out nr int, out name text)returns setof record as $$select ∗ from autor;$$ language sql;CREATE FUNCTIONtest=# select ∗ from autor_lesen_setofrec_korr1();nr | name

−−+−−−−−−−−−−

2 | wenk1 | pfeiffer

(2 Zeilen)

test=# select ∗ from autor_lesen_setofrec_korr1() where nr =1;nr | name

−−+−−−−−−−−−−

1 | pfeiffer(1 Zeile )

5

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 | 1011108 | Wendtland | Hans | 1012108 | 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,'+');

6

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 an-gegeben1.

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 wieTabelle kunden

kundenspalte kunden.vorname%TYPE ; Variable vom gleichen Typ wie Spalte vor-name in Tabelle kunden; Vorteil: ändertsich in der Spalte der Datentyp (alter),dann muss der Variablentyp nicht geän-dert werden

arow RECORD; ähnlich ROWTYPE, nur ohne Struktur

zahl1 alias for $1 für Parameter kann ein Alias vergebenwerden, vor allem dann, wenn viele Para-meter übergeben werden (übersichtlicherals $1, $2, $3, etc.)

Tabelle 1: Variablen Datentypen

Ein einfaches Beispiel, das zwei Zahlen dividiert

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;

beginreturn 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 dannverwendet 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 einfachesreturn.

1sollte plpgsql nicht vorhanden sein, wird sie mit dem Befehl »create language plpgsql« nachinstalliert

7

PostgreSQL E3A

Im nächsten Beispiel wird zusätzlich eine Variable deklariert

create function f_gross(text) returns text as $$declare

wert1 alias for $1;wert_gross text; /∗ Hilfsvariable ∗/

beginwert_gross := UPPER(wert1); /∗ Variable wert1 in wert_gross umspeichern ∗/return wert_gross;

end $$ language plpgsql;CREATE FUNCTIONtest=# 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 imeigentlichen Programmblock mittels UPPPER umgewandelt wird. Einer Variable wird mittels :=ein Wert zugewiesen.Jedes Wort, das einem Variablennamen entspricht wird interpretiert. Daher funktioniert folgen-des Programm nicht:

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 FUNCTIONtest=# select f_artikel1(1);f_artikel1

−−−−−−−−−−−−

(1 Zeile )

Folgendes Programm aber schon:

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

8

PostgreSQL E3A

test=# select f_artikel(1);f_artikel

−−−−−−−−−−−

maus(1 Zeile )

2.1.3 Fehler abfragen

Noch mals zurück zum Beispiel mit der Division. Hier sollte ja bei einem Versuch, durch 0 zudividieren, ein Hinweis aufscheinen, dass dies nicht erlaubt ist.

create or replace function division( numeric,numeric ) returns numeric as $$declare

zahl1 alias for $1 ;zahl2 alias for $2 ;

beginif 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 Programmbricht ab, ansonsten wird zahl1 durch zahl2 dividiert. Die Ausgabe sieht so aus.

test=# select division(4,0);FEHLER: division durch 0 nicht definierttest=#

Letzteres Beispiel lässt sich auch so ausprogrammieren:

create function division1 (numeric, numeric) returns numeric as $$declare

zahl1 alias for $1;zahl2 alias for $2;

beginreturn $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 einHinweis (notice) ausgegeben, dass »division durch 0 nicht definiert« ist. Die möglichenCodes 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 definiertdivision1

−−−−−−−−−−−

(1 Zeile )

test=#

2http://www.postgresql.org/docs/8.4/static/errcodes-appendix.html

9

PostgreSQL E3A

Da eine Funktion einen return-Wert benötigt, muss hier in dem zweiten Beispiel zumindest einreturn null angegeben werden, ansonsten kommt es zu einer unerwünschten Fehlermeldung.Im ersten Programm bricht ja die raise exception ab.

2.2 Vergleiche

Vergleiche in einer plpgsql-Funktion werden mit IF realisiert.

create function zahl_vergleich(integer, integer) returns text as $$declare

zahl1 alias for $1;zahl2 alias for $2;ausgabe text;

beginif 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 FUNCTIONtest=#

Hier wird für die Ausgabe eine Variable namens ausgabe definiert, je nach dem, welche Zahlgrößer ist, wird diese Variable mit einem anderen Wert (Datentyp text) belegt und mittelsreturn ausgegeben.In einer Funktion kann statt return auch mit raise notice gearbeitet werden, der Rückgabe-wert der Funtkion ist dann void.

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 ∗/

beginif 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 FUNCTIONtest=# select division_void(4,2);HINWEIS: 2.0000000000000000division_void

−−−−−−−−−−−−−−−

(1 Zeile )

test=# select division_void(4,0);FEHLER: division durch 0 nicht definierttest=# select division_void(4.2,2.1);HINWEIS: 2.0000000000000000division_void

−−−−−−−−−−−−−−−

(1 Zeile )

10

PostgreSQL E3A

Bei einem regulären Ergebnis wird eine raise notice – also ein Hinweis ausgegeben, bei einerDivision durch 0 eine raise exception; die Funktion gibt void (also nichts) zurück.

2.3 Schleifen

2.3.1 LOOP

create or replace function schleife_loop() returns integer as $$declare zahl int;begin

zahl := 1;loop

zahl := zahl+1; /∗ if zahl > 5 then ∗/exit when zahl > 5; /∗ exit ; ∗/

end loop; /∗ end if ; ∗/return zahl;end; $$ language plpgsql;CREATE FUNCTIONtest=# select schleife_loop();schleife_loop

−−−−−−−−−−−−−−−

6(1 Zeile )

Die Schleife erhöht die Variable zahl ständig um 1 und bricht ab, wenn sie einen Wert > 5erreicht. Das IF im Kommentar arbeitet gleich wie EXIT WHEN. Sollen alle Zahlen von 1 − 5ausgegeben werden, so muss die Funktion umgeschreiben werden:

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 FUNCTIONtest=# select schleife_loop2();HINWEIS: zahl ist 1HINWEIS: zahl ist 2HINWEIS: zahl ist 3HINWEIS: zahl ist 4HINWEIS: zahl ist 5schleife_loop2

−−−−−−−−−−−−−−−−

(1 Zeile )

Will man bei obiger Schleife statt RAISE NOTICE ein RETURN verwenden, so muss die Schleife soumgebaut werden:

11

PostgreSQL E3A

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

2.3.2 FOR

Eine FOR-Schleife läuft innerhalb eines fixen Bereichs (Stichwort Zähler)

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 FUNCTIONtest=# select schleife_for();HINWEIS: 1HINWEIS: 2HINWEIS: 3HINWEIS: 4HINWEIS: 5

schleife_for−−−−−−−−−−−−−−

(1 Zeile )

2.3.3 WHILE

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 1HINWEIS: zahl ist 2HINWEIS: zahl ist 3HINWEIS: zahl ist 4schleife_while

−−−−−−−−−−−−−−−−

(1 Zeile )

12

PostgreSQL E3A

2.4 Anwendungen in der Praxis

Diese Beispiele waren bisher nicht sehr praxisrelevant, daher im Folgenden einige Beispiele, indenen PL/pgSQL-Funktionen angewendet werden. In einer Funktion können alle Befehle, diekein 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 | 12 | laptop | 400.00 | 13 | laserdrucker | 150.99 | 14 | tastatur | 39.99 | 15 | virenschutz | 200.00 | 2

(5 Zeilen)create function f_updartikel(artikeltext text, artikelnummer int) returns void as $$declare

art alias for $1;artnr alias for $2;

beginupdate artikel set bez = art where aid = artnr;

end $$ language plpgsql;CREATE FUNCTIONtest=# select f_updartikel('ratte', 1);f_updartikel

−−−−−−−−−−−−−−

(1 Zeile )

test=# select ∗ from artikel;aid | bez | preis | typ

−−−+−−−−−−−+−−−−+−−−−−

2 | laptop | 400.00 | 13 | laserdrucker | 150.99 | 14 | tastatur | 39.99 | 15 | virenschutz | 200.00 | 21 | 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)

create function f_artikel(id int) returns text as $$declare

art alias for $1;bez text;

beginselect a.bez from artikel a into bez where aid = art; /∗ liefert einzeiliges Ergebnis ∗/

return bez;end $$ language plpgsql;CREATE FUNCTIONtest=# select f_artikel(1);f_artikel

−−−−−−−−−−−

13

PostgreSQL E3A

maus(1 Zeile )

Das RETURN kann auch weggelassen werden (oder als leeres »RETURN« stehen, dafür muss alsParameter mindestens ein OUT definiert werden.

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 FUNCTIONtest=# 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 $$

DECLAREl_name ALIAS FOR $1;f_name ALIAS FOR $2;

BEGINSELECT INTO kunde kundennr FROM kunden

WHERE name = l_name AND vorname = f_name;END;

$$ LANGUAGE plpgsql;

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 kundenloop

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

return query select ∗ from kunden;end $$ language plpgsql;

14

PostgreSQL E3A

Ein Beispiel für eine Funktion mit IN-Parameter, die mehrere Zeilen ausliest (Achtung: derIN-Parameter darf nicht gleich lauten wie die verwendete Spalte !!!)

create function f_bestschleife(in k int) returns setof bestellungen as $$declare

erg bestellungen%ROWTYPE;beginfor erg in select ∗ from bestellungen where kid = $1loop

return next erg;end loop;end $$ language plpgsql;CREATE FUNCTIONtest=# select ∗ from bestellungen where kid = 1;bid | bdat | kid

−−−+−−−−−−−+−−−−−

1 | 2010−08−07 | 12 | 2010−08−07 | 1

Ebenso muss man aufpassen, wenn IN-Parameter gleich lauten, wie eine Spalte. Hier kommt esnicht zum gewünschten Ergebnis:

create function f_bestschleife(in k int) returns setof bestellungen as $$declare

erg bestellungen%ROWTYPE;kid alias for $1; /∗ Variable heisst gleich wie Spalte ∗/

beginfor erg in select ∗ from bestellungen where kid = kid

loopreturn next erg;

end loop;end $$ language plpgsql;CREATE FUNCTIONtest=# select ∗ from f_bestschleife(2);bid | bdat | kid

−−−+−−−−−−−+−−−−−

1 | 2010−08−07 | 12 | 2010−08−07 | 13 | 2010−08−07 | 2

(3 Zeilen)

Hingegen stimmt hier das Ergebnis:

create function f_bestschleife(in k int) returns setof bestellungen as $$declareerg bestellungen%ROWTYPE;

ki alias for $1; /∗ Variable heisst anders als Spalte ∗/begin

for erg in select ∗ from bestellungen where kid = kiloop

return next erg;end loop;end $$ language plpgsql;CREATE FUNCTIONtest=# select ∗ from f_bestschleife(2);bid | bdat | kid

−−−−−+−−−−−−−−−−−−+−−−−−

3 | 2010−08−07 | 2

15

PostgreSQL E3A

(1 Zeile )

Bei Verwendung mehrerer Tabellen genügt ein einfaches rowtype nicht mehr, daher muss eineigener Typ kreiert werden, der die Spalten aus den jeweiligen Tabellen beinhaltet. Dieser Typkann 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 TYPEbestellungen=# create function f_kdbest1(in kundennummer int) returns setof t_kdbest as $$declare

kdnr alias for $1;erg t_kdbest%rowtype;

beginfor erg in /∗ für alle Zeilen des Ergebnisses ... ∗/

select kunden.kundennr, name, vorname , bestellnrfrom kunden, bestellung wherekunden.kundennr = bestellung.kundennr and kunden.kundennr = kdnr

loopreturn next erg; /∗ gib in einer Schleife alle Zeilen der Reihe nach aus ∗/

end loop;end $$ language plpgsql;CREATE FUNCTIONbestellungen=# 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ächli-chen 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

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

nicht ok

create or replace function f_autor_a(anr int) returns table (anr int, name text) as $$declare

autornr alias for $1;begin

16

PostgreSQL E3A

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üssennicht komplett in den Speicher geladen werden, sondern können Zeile für Zeile durchgegangenwerden. 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 bei-spielsweise 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 dieseFunktion kein Ergebnis.Ein Beispiel an folgender Tabelle artikel:

test=# select ∗ from artikel;aid | bez | preis | typ

−−−−−−−−−−−−−−−−−−−−−−−−−−−

2 | laptop | 400.00 | 13 | laserdrucker | 150.99 | 14 | tastatur | 39.99 | 15 | virenschutz | 200.00 | 21 | ratte | 15.99 | 1

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 gebundenerg record; −−typ record zum abspeichern

beginfor erg in select ∗ from artikel order by aidloop

raise info 'tab. artikel : %', erg;−−record ausgebenend loop;open crs for select ∗ from artikel order by aid;−−refcursor

fetch crs into erg; −−zeile in erg speichernraise info 'ergebnis: %', erg;fetch last from crs into erg; −−letzte zeile holenraise info ' letzte zeile : %',erg;

17

PostgreSQL E3A

fetch relative −3 from crs into erg; −−3 zeilen vorher holenraise info 'erg 3 vor letzten zeile : %', erg;

close crs ;open crs_geb; −−gebundenen cursor öffnen

fetch crs_geb into erg; −−in variable speichernraise info 'ergebnis gebundener cursor: %', erg;fetch relative +2 from crs_geb into erg;−−2 zeilen nach vorraise info 'ergebnis gebundener cursor 2 zeilen nachher: %', erg;

close crs_geb; −−schließenreturn 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. Inder Funktion müssen die Parameter dann immer refcursor lauten, ebenso müssen die Rückga-bewerte returns refcursor lauten.

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 FUNCTIONtest=# begin; −−Beginn der TransaktionBEGINtest=# select f_crs1('a'); −−refcursor als Parameterf_crs1

−−−−−−−−

a(1 Zeile )

test=# fetch next from a; −−1. zeile in a ladenpid | name | titel | aid

−−−−−+−−−−−−+−−−−−−−+−−−−−

1 | hans | Herr |(1 Zeile )

test=# fetch next from a; −−nächste zeile in a ladenpid | name | titel | aid

−−−−−+−−−−−−+−−−−−−−+−−−−−

2 | sepp | |(1 Zeile )

18

PostgreSQL E3A

test=# fetch prior from a; −−vorige zeile in a ladenpid | name | titel | aid

−−−−−+−−−−−−+−−−−−−−+−−−−−

1 | hans | Herr |(1 Zeile )

test=# commit; −−transaktion abschließen (bestätigen)COMMITtest=#

Ein Cursor kann auch mit Parametern verwendet werden:

create function f_crs_para(integer) returns setof person as $$declare

crs cursor (ID integer) for select ∗ from person where aid = ID; −−cursor mit parameterprs person%ROWTYPE; −−ganze zeile in einem blocknr alias for $1;

beginopen crs(nr); −öffnen mit Parameterloop

fetch crs into prs; −−in variable ladenexit when not found; −−nach letzter zeile aussteigenreturn 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;

BEGINFOR erg IN SELECT titel, artikelpreis FROM artikel

WHERE artikelpreis < prsLOOP

−−implizites OPEN und FETCHbez := erg. titel || ' ' || erg. artikelpreis ;RETURN NEXT bez;

END LOOP; −−implizites CLOSERETURN;

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 ||' '|| artikelpreisFROM artikelWHERE artikelpreis < prs;

bez artikel . titel %TYPE;BEGIN

OPEN erg;FETCH erg INTO bez;

19

PostgreSQL E3A

WHILE FOUNDLOOP

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 ein-tritt. Trigger sind ähnlich den Rules mit dem Unterschied, dass ein Trigger in PostgreSQLimmer 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

2.6.1 Per-Row-Trigger

Zuerst muss die Funktion erstellt werden, danach der Trigger, der die Funktion aufruft. EinBeispiel: In der Tabelle bestellungen soll für neue Werte nur ein Datum ≥ das heutige erlaubtsein.

test=# select ∗ from bestellungen;bid | bdat | kid

−−−−−+−−−−−−−−−−−−+−−−−−

1 | 2010−08−07 | 12 | 2010−08−07 | 13 | 2010−08−07 | 24 | 2010−11−25 | 2

create function f_check_bestdat() returns trigger as $$begin

if (new.bdat) < now() then −−wenn neues Datum kleiner heutiges istnew.bdat := now(); −−auf heutiges setzen

end if;return new;

end;$$ language plpgsql;

create trigger trig_checkbdat before insert or update on bestellungenfor each rowexecute procedure f_check_bestdat();

Der Trigger muss klarerweise vor – before dem Einfügen/Aktualisieren aktiv werden, nahcherwä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 (12. Jänner 2012) führt zu folgendemErgebnis:

test=# insert into bestellungen values(5, '2010−10−24',2);INSERT 0 1test=# select ∗ from bestellungen;bid | bdat | kid

−−−−−+−−−−−−−−−−−−+−−−−−

1 | 2010−08−07 | 12 | 2010−08−07 | 1

20

PostgreSQL E3A

3 | 2010−08−07 | 24 | 2010−11−25 | 25 | 2010−11−25 | 2

(5 Zeilen)

Aktionen, die nach dem Einfügen stattfinden, könnten zum Beispiel Einträge in Log-Tabellensein. NEW wird verwendet beim Einfügen von neuen oder Ändern mit enuen Werten, OLD wirdverwendet, 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 diesenVorgang 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

−−−−

123

(3 Zeilen)

create function f_deltest2() returns trigger as $$begin

insert into test2_log values(now(), session_user);return null;

end $$ language plpgsql;

create trigger trig_deltest2 after delete on test2 for each statement execute procedure f_deltest2();

21

PostgreSQL E3A

Übungen:

• Erstelle folgende Tabellen (FK – PK-Verbindung über kid):

test=# select ∗ from kunden;kid | name | anzahl_best

−−−−−+−−−−−−−−+−−−−−−−−−−−−−

1 | meier | 03 | dorfer | 02 | huber | 0

(3 Zeilen)

test=# select ∗ from bestellungen;bid | bdat | kid

−−−−−+−−−−−−−−−−−−+−−−−−

1 | 2010−08−07 | 12 | 2010−08−07 | 13 | 2010−08−07 | 24 | 2010−11−25 | 25 | 2010−11−25 | 2

Schreibe eine Triggerfunktion, die nach einem INSERT in die Tabelle bestellungen denWert 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 huberINSERT 0 1test=# select ∗ from kunden;kid | name | anzahl_best

−−−−−+−−−−−−−−+−−−−−−−−−−−−−

1 | meier | 03 | dorfer | 02 | 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 Fehlerprü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 eingetragenwerden.

−−INSERTtest=# insert into ma (name,gehalt) values('huber',2000);

22

PostgreSQL E3A

INSERT 0 1test=# select ∗ from ma;name | gehalt | datum_aenderung | user_aenderung

−−−−−−−+−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−

huber | 2000 | 2010−11−25 21:38:33.740366 | postgres−−UPDATEtest=# update ma set gehalt = 3000 where name = 'huber';UPDATE 1test=# select ∗ from ma;name | gehalt | datum_aenderung | user_aenderung

−−−−−−−+−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−

huber | 3000 | 2010−11−25 22:00:15.376363 | postgres

1. Lösung

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';

CREATE TRIGGER trig_kundenbestAFTER INSERTON bestellungenFOR EACH ROWEXECUTE PROCEDURE f_trig_kundenbest();

2. Lösung

CREATE FUNCTION ma_check()RETURNS trigger AS

$BODY$BEGIN

IF NEW.name IS NULL THENRAISE 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';

CREATE TRIGGER ma_check_trigBEFORE INSERT OR UPDATEON maFOR EACH ROWEXECUTE PROCEDURE ma_check();

23