Re: plpgsql function returning SETOF

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql function returning SETOF
Date: 2005-12-21 17:14:22
Message-ID: 20051221171422.GA2801@webserv.wug-glas.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 ===

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jaime Casanova 2005-12-21 17:19:32 Re: transfer database from Linux to Windows
Previous Message Daniel T. Staal 2005-12-21 17:13:06 Re: Questions about server.