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.kretschmer@schollglas.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?

Thanks a lot!
Johannes