Re: plpgsql function returning SETOF

am  21.12.2005, um 16:54:43 +0100 mailte Johannes BrXgmann folgendes:
> Hello all novices and experts,
>
> I didn't understand how to create a function, which returns a varying
> number of records. Worse, it seems, that I didn't understand the SETOF
> feature at all... Two examples:
> 2.)
>
> similar to the above function, the below have been declared; each of
> them returns exactly one value of type timestamp with time zone.
>
>       CREATE TYPE feiertag AS (bezeichnung VARCHAR(100), datum TIMESTAMP WITH TIME ZONE);
>
>       CREATE OR REPLACE FUNCTION feiertage(TIMESTAMP WITH TIME ZONE)
>       RETURNS SETOF feiertag
>       CALLED ON NULL INPUT AS $$
>       DECLARE
>            in_ts timestamp with time zone;
>       BEGIN
>            IF $1 IS NULL
>                THEN in_ts := localtimestamp(0);
>                ELSE in_ts := $1;
>            END IF;
>
>               SELECT 'Allerheiligen             ', allerheiligen(in_ts)
>           UNION SELECT 'Aschermittwoch            ', aschermittwoch(in_ts)
>         ...
>           UNION SELECT 'Tag der deutschen Einheit ', tagderdeutscheneinheit(in_ts)
>           UNION SELECT 'Zweiter Weihnachtstag     ', zweiterweihnachtstag(in_ts)  ;
>       END;
>       $$ LANGUAGE plpgsql;
>
>
> a result "table" like this is what I want:
>
>        bezeichnung     | datum
>       -----------------+-------
>        Allerheiligen   | ....

You should return your rows ;-)

Visit http://www.varlena.com/GeneralBits/26, Chapter ' Returning Sets',
for a example for a SRF written in plpgsql.


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===