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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Johannes Brgmann | 2005-12-21 18:28:43 | Re: plpgsql function returning SETOF |
Previous Message | operationsengineer1 | 2005-12-21 17:40:10 | Re: Postgresql v 8.0.1-3 problems |