Thread: plpgsql function returning SETOF
Hello all novices and experts,
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... Two examples:
1.)
should give two records in the year 1990: 1990.06.17 and 1990.10.03,
all other years have minimum 1 NULL record (so at some later point I
can filter with WHERE or so; only for timezone = 'CET' and datestyle =
'German')
CREATE OR REPLACE FUNCTION tagderdeutscheneinheit(TIMESTAMP WITH TIME ZONE)
RETURNS SETOF TIMESTAMP WITH TIME ZONE
CALLED ON NULL INPUT STABLE AS $$
DECLARE
in_ts timestamp with time zone;
rest_ts varchar(14);
yyyy smallint;
BEGIN
IF $1 IS NULL
THEN in_ts := localtimestamp(0);
ELSE in_ts := $1;
END IF;
yyyy := to_number(date_part('year',in_ts),'9999');
rest_ts := to_char(date_part('year',in_ts),'9999') || to_char(date_part('hour',in_ts),'99')
|| to_char(date_part('minute',in_ts),'99') || to_char(date_part('seconds',in_ts),'99');
SELECT CASE WHEN yyyy > 1951 AND yyyy <= 1990 THEN to_timestamp('03.10.' || rest_ts,'DD MM YYYY HH24 MI SS')
ELSE NULL
END
UNION ALL
SELECT CASE WHEN yyyy >= 1990 THEN to_timestamp('17.06.' || rest_ts,'DD MM YYYY HH24 MI SS')
ELSE NULL
END;
END;
$$ LANGUAGE plpgsql;
what i get:
ERROR: SELECT query has no destination for result data
2.)
similar to the above function, the below have been declared; each of
them returns exactly one value of type timestamp with time zone.
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;
BEGIN
IF $1 IS NULL
THEN in_ts := localtimestamp(0);
ELSE in_ts := $1;
END IF;
SELECT 'Allerheiligen ', allerheiligen(in_ts)
UNION SELECT 'Aschermittwoch ', aschermittwoch(in_ts)
...
UNION SELECT 'Tag der deutschen Einheit ', tagderdeutscheneinheit(in_ts)
UNION SELECT 'Zweiter Weihnachtstag ', zweiterweihnachtstag(in_ts) ;
END;
$$ LANGUAGE plpgsql;
a result "table" like this is what I want:
bezeichnung | datum
-----------------+-------
Allerheiligen | ....
what i get:
bruegmann@traffic_nrw=# select feiertage(NULL);
ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "feiertage" line 9 at SQL statement
What I don't understand here is: this is a fixed number of records, a
composit type for setof has been declared, so what else is missing or
is wrong?
Thanks for any help,
Johannes
am 21.12.2005, um 16:54:43 +0100 mailte Johannes BrXgmann folgendes: > Hello all novices and experts, > > 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... Two examples: > 2.) > > similar to the above function, the below have been declared; each of > them returns exactly one value of type timestamp with time zone. > > 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; > BEGIN > IF $1 IS NULL > THEN in_ts := localtimestamp(0); > ELSE in_ts := $1; > END IF; > > SELECT 'Allerheiligen ', allerheiligen(in_ts) > UNION SELECT 'Aschermittwoch ', aschermittwoch(in_ts) > ... > UNION SELECT 'Tag der deutschen Einheit ', tagderdeutscheneinheit(in_ts) > UNION SELECT 'Zweiter Weihnachtstag ', zweiterweihnachtstag(in_ts) ; > END; > $$ LANGUAGE plpgsql; > > > a result "table" like this is what I want: > > bezeichnung | datum > -----------------+------- > Allerheiligen | .... You should return your rows ;-) Visit http://www.varlena.com/GeneralBits/26, Chapter ' Returning Sets', for a example for a SRF written in plpgsql. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
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
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?
sorry, i missed the error message:
bruegmann@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
Johannes Brügmann <johannes@jottbee.org> schrieb:
> 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 ]
If you write the function in the language 'sql', then this is right.
But in your function there are things like 'if...', so you need plpgsql.
>
> But this is SQL isn't it, ;-)? And SQL doesn't support timestamp, does
> it? (AAaaarrggghhhh...)
???
timestamp and timestamptz are valid types in SQL.
> 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)
> ...
> END;
> $$ LANGUAGE plpgsql;
>
> Where is the bug now?
I can't see a error-message, sorry ;-)
Btw.: we have a german mailing list too ;-)
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
"Johannes Br�gmann" <johannes@jottbee.org> writes:
> sorry, i missed the error message:
> bruegmann@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
You need to do
select * from your_function(...);
not just
select your_function(...);
regards, tom lane
Hello Andreas,
hello novices and experts,
thanks again for your immediate help!
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Johannes Brügmann <johannes@jottbee.org> schrieb:
>> 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 ]
>
> If you write the function in the language 'sql', then this is right.
> But in your function there are things like 'if...', so you need plpgsql.
This is what i tried after understanding it with your help, but i got
a syntax error in the DECLARE part just before timestamptz, so i
thought the type wouldn't be supported by language 'sql'. I didn't
know that the IF control expressions where the bad guys. Maybe I try
it again in language 'sql', because IF should be replacable in what i
want.
>> But this is SQL isn't it, ;-)? And SQL doesn't support timestamp, does
>> it? (AAaaarrggghhhh...)
>
> ???
> timestamp and timestamptz are valid types in SQL.
yes, i wrote it before i thought about it; sorry.
>> 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)
>> ...
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> Where is the bug now?
>
> I can't see a error-message, sorry ;-)
yes, i recognized my mistake already.. The other posting contains the solution.
> Btw.: we have a german mailing list too ;-)
Aah, next time!
Thanks you very much for your patience and the immediate help
Johannes
Hello Tom, hello novices and experts, Tom Lane <tgl@sss.pgh.pa.us> writes: > "Johannes Brügmann" <johannes@jottbee.org> writes: >> sorry, i missed the error message: > >> bruegmann@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 > > You need to do > select * from your_function(...); > not just > select your_function(...); yes, that's it! Now it works, yeaaaah! Thanks a lot to you, Tom, for your immediate help! Johannes