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 18:28:43
Message-ID: 5zirti1ezo.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?

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


In response to

Responses

pgsql-novice by date

Next:From: Andreas KretschmerDate: 2005-12-21 18:44:57
Subject: Re: plpgsql function returning SETOF
Previous:From: Johannes BrgmannDate: 2005-12-21 17:54:37
Subject: Re: plpgsql function returning SETOF

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