Ich habe nach viel Versuch und noch mehr Irrtum schließlich eine Funktion mit dem Rückgabewert TABLE(feld1, feld2, …) hinbekommen, die tatsächlich tut, was sie soll, und jetzt bin ich auf der Suche nach einer Abkürzungsmöglichkeit.
Die Funktion sieht erst mal so aus:
-- Function: func_test(integer)
-- DROP FUNCTION func_test(integer);
CREATE OR REPLACE FUNCTION func_test (eidn integer)
RETURNS TABLE(vid integer, vname character varying, vzz integer) AS
$BODY$
BEGIN
FOR vid, vname IN
SELECT m.l_id, m.descr FROM master m
JOIN lookup l ON m.l_id = l.idn
WHERE m.idn = eidn
LOOP
SELECT COUNT(*) FROM products p WHERE p.l_id = vid
INTO vzz;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
Schön und recht für eine Rückgabetabelle mit drei Feldern. Aber für zehn oder mehr? Eine funktionierende Alternative:
DECLARE vrec RECORD;
...
FOR vrec IN
... wie gehabt bis vor "RETURN NEXT";
vid = vrec.l_id;
vname = vrec.descr;
RETURN NEXT;
Aber das ist eher noch etwas länger, keinesfalls eine Abkürzung. Die Datenbank enthält eine View-Definition mit den gleichen Feldbezeichnungen und -Typen, lässt sich das hier nutzen?
So ungefähr. Wegen der Bemerkung in der Dokumentation über die Portabilität, 35.4.9. SQL Functions Returning TABLE, wollte ich lieber RETURNS TABLE als RETURNS SETOF RECORD benutzen. Inzwischen habe ich ausprobiert, dass man auch in “RETURNS TABLE” so einen Datensatz-Typ angeben kann:
CREATE FUNCTION get_foo() RETURNS TABLE(foorec foo) AS
$$
BEGIN
FOR foorec IN
SELECT c1, c2, c3 FROM foo
LOOP;
-- irgendetwas mit den Werten anstellen
RETURN NEXT;
END LOOP;
RETURN;
END;
Kann man damit, glaube ich, als gelöst betrachten.
Na ja, nicht so ganz. Mit dieser Konstruktion (RETURN TABLE(foorec foo)) handelt man sich ein Problem mit pgAdmin ein: im SQL-Feld erscheint
CREATE FUNCTION get_foo()
RETURNS SETOF foo AS
$$
...
Rest wie gehabt. Genau so, wenn man zum Ändern der Funktion “CREATE Script …” im Kontextmenü wählt. Das hat den Haken, dass bei dieser Definition des Rückgabewertes die Schleifenvariable foorec deklariert werden müsste, das tut das Re-Engineering von pgAdmin aber nicht.
Da könnte man verschiedene Konsequenzen ziehen:
a) die von pgAdmin verursachte Änderung bei jeder Bearbeitung der Funktion wieder rückgängig machen;
b) dieses Werkzeug nicht für die Bearbeitung von Funktionen benutzen, sondern dafür eigene Skripte schreiben und per psql ausführen lassen (oder ein Skript per pg_dump herstellen, da bleibt die Funktion, wie man sie geschrieben hat);
c) der höheren Weisheit der pgAdmin-Entwickler vertrauen und diese Konstruktion lieber doch nicht verwenden, sondern entweder mit SETOF foo und der nötigen Deklaration arbeiten oder in RETURN TABLE die Felder einzeln aufführen und dies auch im Funktionskörper tun (das lässt pgAdmin so stehen).
Kann mir jemand gute Argumente für eine dieser drei Möglichkeiten nennen? Für Variante a) wahrscheinlich eher nicht.