Re: plpgsql function returning SETOF

Hello Andreas,
hello novices and experts,

> first of all thank you very much for your immediate response! The hint
> is great but surprising to me.
>
> "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> writes:
>
>> am 21.12.2005, um 16:54:43 +0100 mailte Johannes BrXgmann folgendes:
>>>
>>> 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...
>>>
>>> a result "table" like this is what I want:
>>>
>>> bezeichnung | datum
>>> -----------------+-------
>>> Allerheiligen | ....
>>
>> You should return your rows ;-)
>
> Thank you very much!
>
> I always had this in mind:
>
> ,----[ ${doc}/postgresql-8.0.3/html/xfunc-sql.html ]
> | 31.4.4. SQL Functions Returning Sets
> |
> | When an SQL function is declared as returning SETOF sometype, the
> | function's final SELECT query is executed to completion, and each row
> | it outputs is returned as an element of the result set.
> `----[ end ]
>
> But this is SQL isn't it, ;-)? And SQL doesn't support timestamp, does
> it? (AAaaarrggghhhh...)
>
> A new problem is now, that i still can't get it to work after all:
>
> 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;
> curr feiertag%ROWTYPE;
> r RECORD;
> BEGIN
> IF $1 IS NULL
> THEN in_ts := localtimestamp(0);
> ELSE in_ts := $1;
> END IF;
>
> FOR r IN SELECT f.b AS b, f.d AS d
> FROM (
> SELECT 'Allerheiligen ' AS b, allerheiligen(in_ts) AS d
> UNION SELECT 'Aschermittwoch ' AS b, aschermittwoch(in_ts) AS d
> ...
> UNION SELECT 'Tag der deutschen Einheit ' AS b, tagderdeutscheneinheit(in_ts) AS d
> UNION SELECT 'Zweiter Weihnachtstag ' AS b, zweiterweihnachtstag(in_ts) AS d) AS f
> LOOP
> curr.bezeichnung := r.b;
> curr.datum := r.d;
> RETURN NEXT curr;
> END LOOP;
> RETURN;
>
> END;
> $$ LANGUAGE plpgsql;
>
> Where is the bug now?

sorry, i missed the error message:

bruegmann(at)traffic_nrw=# select feiertage(NULL);
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "feiertage" line 30 at return next

Thanks in advance,
Johannes