4. Serverseitige Funktionen

PostgreSQL bietet selbst eine Fülle von Funktionen, aber man kann diese auch selbst erweitern. Dazu stehen mehrere Programmiersprachen bereit, von C über reinem SQL und PL/pgSQL bis Perl, Java und Tcl.

Die erste Funktion soll es für uns einfacher machen, anhand des Typs einer Buchung zu erkennen, ob es eine Zu- oder Abbuchung ist. Die anderen Funktionen helfen uns, die aktuellen Salden darzustellen.

001 --
002 -- eine Hilfsfunktion, wir werden sie noch brauchen
003 -- sie wird uns Schreibarbeit ersparen
004 --
005 CREATE OR REPLACE FUNCTION get_saldo (CHAR(1), DECIMAL(10,2)) RETURNS DECIMAL(10,2) AS '
006 DECLARE
007         typ ALIAS FOR $1;
008         wert ALIAS FOR $2;
009 BEGIN
010         IF typ ~ ''Z'' THEN
011                 RETURN wert;
012         ELSE
013                 RETURN wert * -1;
014         END IF;
015 END; '
016 LANGUAGE plpgsql;
017 
018 
019 --
020 -- Wir benötigen eine persistente Variable, möchten aber nicht auf eine
021 -- externe Programmiersprache wie Perl oder TCL zurückgreifen, die uns
022 -- diese Möglichkeit von Haus aus bieten würden
023 -- aus diesem Grund definieren wir eine temporäre Tabelle, die PostgreSQL
024 -- selbstständig am Ende der Session wieder löscht
025 --
026 CREATE OR REPLACE FUNCTION prepare_saldo() RETURNS VOID AS '
027         BEGIN
028 
029           -- sicherstellen, das diese Tabelle in dieser Session noch
030           -- nicht existiert
031           BEGIN
032             EXECUTE ''DROP TABLE prev_saldo'';
033             -- den Fehler, falls die Tabelle nicht existierte, abfangen
034             EXCEPTION WHEN UNDEFINED_TABLE THEN
035               -- do nothing
036           END;
037 
038           -- Tabelle erstellen
039           EXECUTE ''CREATE TEMPORARY TABLE prev_saldo (
040                      id     INTEGER          NOT NULL,
041                      saldo  NUMERIC(10,2)    NOT NULL
042                     )'';
043 
044           -- und mit einem Wert füllen
045           EXECUTE ''INSERT INTO prev_saldo (id, saldo) VALUES (1, 0.0)'';
046           RETURN;
047         END;
048 ' LANGUAGE plpgsql VOLATILE;
049 
050 
051 --
052 -- wir wollen den Saldo zu einem bestimmten Zeitpunkt innerhalb unserer
053 -- Ausgabe wissen, dazu erzeugen wir eine Funktion, die den Wert der aktuellen
054 -- Buchung erhält und als Ergebnis den Gesamtsaldo zurückliefert
055 -- Dafür benutzen wir die soeben erstellte temporäre Tabelle, um den
056 -- Wert zwischen den Aufrufen zu speichern
057 --
058 CREATE OR REPLACE FUNCTION prev_saldo(NUMERIC(10,2)) RETURNS NUMERIC(10,2) AS '
059         DECLARE
060           in_saldo ALIAS FOR $1;
061           out_saldo NUMERIC(10,2);
062           saldo_rec RECORD;
063           query TEXT;
064         BEGIN
065 
066           -- aktuellen Buchungswert zu gespeichertem Wert addieren
067           -- oder abziehen
068           EXECUTE ''UPDATE prev_saldo SET saldo = saldo + '' || in_saldo || '' WHERE id=1'';
069 
070           -- den neuen aktuellen Wert holen
071           -- da PostgreSQL sich den Query Plan merkt, wir aber eventuell
072           -- die temporäre Tabelle neu erstellt haben, brauchen wir einen
073           -- kleinen Umweg
074           query := ''SELECT saldo FROM prev_saldo WHERE id=1'';
075           FOR saldo_rec IN EXECUTE query LOOP
076             -- wird nur einmal durchlaufen, da prev_saldo nur einen Wert liefert
077             out_saldo := saldo_rec.saldo;
078           END LOOP;
079 
080           -- und zurückliefern
081           RETURN out_saldo;
082         END;
083 ' LANGUAGE plpgsql VOLATILE;
084 
085 
086 --
087 -- der Einfachkeit halber fassen wir das mal zusammen
088 -- Unsere Funktion soll eine Liste von Werten zurückgeben, das ist eine
089 -- SRF-Funktion (Set Returning Function)
090 -- Für diese müssen wir uns erst einmal einen neuen Datentyp definieren,
091 -- den wir an unser gewünschtes Format anpassen
092 --
093 CREATE TYPE saldeninfo AS (kontonr    BIGINT, 
094                            zeitpunkt  TIMESTAMPTZ, 
095                            zugang     NUMERIC(10,2), 
096                            abgang     NUMERIC(10,2),
097                            text       VARCHAR,
098                            saldo      NUMERIC(10,2));
099 
100 
101 --
102 -- Nun die Funktion, sie ist in der Sprache SQL definiert
103 --
104 CREATE OR REPLACE FUNCTION saldeninfo(INTEGER) RETURNS SETOF saldeninfo AS '
105         SELECT prepare_saldo();
106         SELECT kontonr, ts,
107                CASE WHEN typ = ''Z'' THEN wert WHEN typ = ''A'' THEN NULL END AS zugang,
108                CASE WHEN typ = ''Z'' THEN NULL WHEN typ = ''A'' THEN wert END AS abgang,
109                text,
110                prev_saldo(get_saldo(typ, wert))::NUMERIC(10,2) AS saldo
111           FROM (SELECT kontonr, ts, typ, wert, text
112                   FROM buchungen
113                  WHERE kontonr = $1 ORDER BY ts) AS x;
114 '
115 LANGUAGE sql VOLATILE;

Links

CREATE FUNCTION Übersicht Sprachen PL/pgSQL