Skip site navigation (1) Skip section navigation (2)

Re: plpgsql function returning SETOF

From: "Johannes Brgmann" <johannes(at)jottbee(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql function returning SETOF
Date: 2005-12-21 17:54:37
Message-ID: 5zslsm1gki.fsf@jottbee.net (view raw or flat)
Thread:
Lists: pgsql-novice
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?

Thanks a lot!
Johannes


In response to

Responses

pgsql-novice by date

Next:From: Johannes BrgmannDate: 2005-12-21 18:28:43
Subject: Re: plpgsql function returning SETOF
Previous:From: operationsengineer1Date: 2005-12-21 17:40:10
Subject: Re: Postgresql v 8.0.1-3 problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group