PostgreSQL 16. November 2010 E3A Übung zu PostgreSQL PL/pgSQL Verwende die Datenbank aus den letzten Stunden (bestellungen oder so). 1. Schreibe eine Funktion, die die Artikel mit ihrer zugehörigen Kategorie ausgibt. Verwende die Tabellen artikel sowie kategorie. Die Ausgabe soll in etwa so aussehen: bestellungen=# select f_artkat(11, 14); f_artkat ---------------------------------------------------------------------------------------("Word 2007 Basis","An Beispielen lernen. Mit Aufgaben üben..",Internet-Technologie) ("Excel 2007 Basis","An Beispielen lernen. Mit Aufgaben üben...",Internet-Technologie) ("AJAX - Frische Ansätze für das Web-Design",,"MS Office") ("My SQL",,Wirtschaft) (4 Zeilen) bestellungen=# select * from f_artkat(11, 14); titel | untertitel | kategoriename -------------------------------------------+--------------------------------------------+------------------Word 2007 Basis | An Beispielen lernen. Mit Aufgaben üben.. | Internet-Technologie Excel 2007 Basis | An Beispielen lernen. Mit Aufgaben üben... | Internet-Technologie AJAX - Frische Ansätze für das Web-Design | | MS Office My SQL | | Wirtschaft (4 Zeilen) Als Parameter werden Artikelnummern in einem Bereich angegeben, in diesem Beispiel werden von den Artikeln mit den Nummern 11 bis 14 die Werte ausgegeben. Da die Werte aus zwei Tabellen stammen, verwende dazu eine eigene Kategorie (siehe http://www.multiaugustinum.com/~guch/images/1/1f/Udf_funktionen_postgresql.pdf) 2. Erstelle eine Tabelle mit folgendem Aussehen: test=# select * from autor_test order by aid; aid | name -----+-------4 | fritz 5 | maxi 6 | mitzi Schreibe eine Funktion, die die Werte bei Vorhandensein aktualisiert. Sind sie jedoch nicht vorhanden, sollen sie neu eingetragen werden. Angenommen, ich rufe die Funktion so auf (Abb. 1), dann erhalte ich folgendes Ergebnis (Abb. 2), (weil der Wert 6 schon in der Tabelle war). test=# select * from autor_test; aid | name -----+-----------4 | fritz 5 | maxi 6 | mitzt-tant test=# select f_autortest1(6, 'mitzt-tant'); f_autortest1 ---------------(6,mitzt-tant) (1 Zeile) Abbildung 1: Aufruf der Funktion Abbildung 2: Ergebnis der Funktion 1 PostgreSQL 16. November 2010 E3A Rufe ich die Funktion so auf (Abb. 3), erhalte ich folgendes Ergebnis (Abb. 4), test=# select * from autor_test; aid | name -----+------------4 | fritz 5 | maxi 6 | mitzt-tant 7 | beppi-onkel test=# select f_autortest1(7, 'beppi-onkel'); f_autortest1 ----------------(7,beppi-onkel) Abbildung 3: Aufruf derFunktion Abbildung 4: Ergebnis dann wird ein Wert neu eingetragen, weil er noch nicht existiert. 3. Wandle die FOR-Schleife aus Punkt 2.3.2 in http://www.multiaugustinum.com/~guch/images/1/1f/Udf_funktionen_postgresql.pdf so ab, dass das Ergebnis mit RETURN (Abb. 6) statt mit RAISE (Abb. 5) ausgegeben wird. Tip: Im CREATE-Teil der Funktion genügt nicht mehr ein einfaches return, da mehrere Werte ausgegeben werden (Stichwort setof). test=# select schleife_for1(); schleife_for1 --------------1 2 3 4 5 (5 Zeilen) test=# select schleife_for(); HINWEIS: 1 HINWEIS: 2 HINWEIS: 3 HINWEIS: 4 HINWEIS: 5 schleife_for -------------- Abbildung 6: mit RETURN Abbildung 5: mit RAISE 4. Schreibe eine Funktion, die von einem bestimmten Artikel den Netto- und Bruttopreis ausgibt. Als Parameter werden die Artikelnummer und der Steuersatz angegeben. Die Ausgabe soll so aussehen (Beispiel mit Artikel 11 und 20% Steuer). bestellungen=# select * from f_artpreis(11, 20); artikelnummer | netto | brutto ---------------+-------+--------------------------11 | 14.95 | 17.9400000000000000000000 (1 Zeile) 5. Schreibe eine Funktion, die die Kunden in einem Bereich auflistet. Wenn der Vorname leer ist, dann soll statt dessen die eMail-Adresse ausgegeben werden. In diesem Beispiel werden die Kunden von 100 bis 108 aufgelistet (1. und 2. Parameter), gleichzeitig müssen sie seit mindestens 2005 Kunde sein (3. Parameter). Kunde 103 und 106 haben keinen Vornamen, daher wird die eMail-Adresse ausgegeben. [ Verwende eine Schleife ] bestellungen=# select f_kunden_email(100, 108, 2005); f_kunden_email ---------------------------------------------100 * Name: Müller, Hans 102 * Name: Meyer, Richard 103 * Firma: Firma * email: Beta-Design 104 * Name: Lange, Anna 105 * Name: Meier, Bernd 106 * Firma: Firma * email: Damaschke-Kleber 107 * Name: Beckman, Daniel 108 * Name: Wendtland, Hans (8 Zeilen) 2 PostgreSQL 16. November 2010 E3A 1. Lösung bestellungen=# CREATE OR REPLACE FUNCTION f_artkat(integer, integer) RETURNS SETOF t_artkat AS $BODY$ declare ar1 alias for $1; ar2 alias for $2; erg t_artkat%rowtype; begin for erg in select titel, untertitel, kategoriename from artikel, kategorie where artikel.kategorienr = kategorie.kategorienr and artikel.artikelnr between ar1 and ar2 loop return next erg; end loop; end $BODY$ LANGUAGE 'plpgsql'; 2. Lösung test=# CREATE OR REPLACE FUNCTION f_autortest1(integer, text) RETURNS record AS $BODY$ declare nummer alias for $1; namen alias for $2; anz int; autortest record; begin select count(*) into anz from autor_test where aid = nummer; if anz = 0 then insert into autor_test values(nummer, namen); else update autor_test set aid = nummer, name = namen where aid = nummer; end if; select aid, name into autortest from autor_test where aid = nummer; return autortest; end $BODY$ 3. Lösung test=# CREATE OR REPLACE FUNCTION schleife_for1() RETURNS SETOF integer AS $BODY$ declare zahl int; begin zahl := 1; for zahl in 1..5 loop /* 1 bis 5 */ return query select zahl; end loop; --return null; end; $BODY$ LANGUAGE 'plpgsql'; 4. Lösung bestellungen=# CREATE FUNCTION f_artpreis(IN artnr integer, IN steuer numeric, OUT artikelnummer integer, OUT netto numeric, OUT brutto numeric) RETURNS SETOF record AS $BODY$ declare artnr alias for $1; steuer alias for $2; begin return query select artikelnr, artikelpreis, (artikelpreis + artikelpreis*(steuer/100)) from artikel where artikelnr = artnr; end $BODY$ LANGUAGE 'plpgsql'; 3 PostgreSQL 16. November 2010 E3A 5. Lösung bestellungen=# CREATE FUNCTION f_kunden_email1(integer, integer, integer) RETURNS SETOF text AS $BODY$ DECLARE kundennr1 alias FOR $1; kundennr2 alias FOR $2; jahr alias for $3; ergebnis Text; zielzeile kunden%ROWTYPE; BEGIN FOR zielzeile IN SELECT * FROM kunden WHERE kundennr BETWEEN kundennr1 AND kundennr2 and EXTRACT(year FROM kundeseit) > jahr LOOP if zielzeile.vorname is NULL --or zielzeile.name is null then ergebnis := zielzeile.kundennr || ' * Firma: '||zielzeile.firma|| ' * email: ' ||zielzeile.email; RETURN next ergebnis; else ergebnis := zielzeile.kundennr || ' * Name: ' || zielzeile.name || ', ' || zielzeile.vorname; RETURN next ergebnis; end if; /*ergebnis := zielzeile.kundennr || ' * ' || zielzeile.email; RETURN NEXT ergebnis;*/ END LOOP; END $BODY$ LANGUAGE 'plpgsql'; 4