Thread: return setof record - strange behavior
Hi everybody. Can anyone enlighten me what's wrong with this function : CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT ro integer, OUT mi integer) RETURNS SETOF record AS $BODY$ DECLARE w record; cy integer := EXTRACT (YEAR FROM current_date); BEGIN FOR w INSELECT (CASE WHEN m > 12 THEN cy + 1 ELSE cy END)::integer, (CASE WHEN m > 12 THEN m - 12 ELSE m END)::integerFROM generate_series(mon + 1, mon + intv) AS m LOOPRETURN next; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; SELECT * FROM month_year(10, 5); Why does it return empty SET ? The amount of rows is correct though .... I'm running 8.1.4 regards mk
The function behaves as expected when in plain SQL, only plpgsql function has the above mentioned problem. regards mk
<div dir="ltr"><br /><br /><div class="gmail_quote">2008/8/4 Marcin Krawczyk <span dir="ltr"><<a href="http://jankes.mk">jankes.mk</a>@<ahref="http://gmail.com">gmail.com</a>></span><br /><blockquote class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Hi everybody.Can anyone enlighten me what's wrong with this function :<br /><br /> CREATE OR REPLACE FUNCTION month_year(moninteger, intv integer, OUT<br /> ro integer, OUT mi integer)<br /> RETURNS SETOF record AS<br /> $BODY$<br/> DECLARE<br /> w record;<br /> cy integer := EXTRACT (YEAR FROM current_date);<br /><br /> BEGIN<br/><br /> FOR w IN<br /> SELECT (CASE WHEN m > 12 THEN cy + 1 ELSE cy END)::integer, (CASE<br /> WHEN m> 12 THEN m - 12 ELSE m END)::integer<br /> FROM generate_series(mon + 1, mon + intv) AS m<br /> LOOP<br /> RETURN next;<br /> END LOOP;<br /><br /> END;<br /><br /> $BODY$<br /> LANGUAGE 'plpgsql' VOLATILE;<br /><br /><br/> SELECT * FROM month_year(10, 5);<br /><br /> Why does it return empty SET ? The amount of rows is correct though....<br /> I'm running 8.1.4<br /><br /> regards<br /> mk<br /><font color="#888888"><br /> --<br /> Sent via pgsql-sqlmailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to yoursubscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br />Hi<br /><br />merlin=#CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer)<br /> RETURNS SETOF record AS<br />$BODY$<br />DECLARE<br/>w record;<br />cy integer := EXTRACT (YEAR FROM current_date);<br /> BEGIN<br />FOR w IN<br /> SELECT (CASE WHEN m > 12 THEN cy + 1 ELSE cy END)::integer, (CASE<br />WHEN m > 12 THEN m - 12 ELSE m END)::integer<br/> FROM generate_series(mon + 1, mon + intv) AS m<br />LOOP<br /> RETURN next w;<br /> END LOOP;<br/>END;<br />$BODY$<br /> LANGUAGE 'plpgsql' VOLATILE;<br /><br />and<br /><br />merlin=# SELECT * FROM month_year(10,5) as (x integer, y integer);<br /> x | y<br />------+----<br /> 2008 | 11<br /> 2008 | 12<br /> 2009 | 1<br /> 2009 | 2<br /> 2009 | 3<br />(5 rows)<br /><br /><br />without output params<br /><br /><br />-- <br />--<br/>Serdecznie pozdrawiam<br /><br />Pawel Socha<br /><a href="mailto:pawel.socha@gmail.com">pawel.socha@gmail.com</a><br/><br />programista/administrator<br /><br />perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%&-;^[%"`-{ a%%s%%$_%ee'<br /></div>
Dzieki za odpowiedz. Ciekawe ze funkcja SQL dziala bez problemu - ale tu juz trzeba wskazac parametry OUT. Thanks for your answer. It's curious that SQL function works as expected - but requires OUT params. pozdrowienia/regards mk 2008/8/4 Pawel Socha <pawel.socha@gmail.com>: > > > 2008/8/4 Marcin Krawczyk <jankes.mk@gmail.com> >> >> Hi everybody. Can anyone enlighten me what's wrong with this function : >> >> CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT >> ro integer, OUT mi integer) >> RETURNS SETOF record AS >> $BODY$ >> DECLARE >> w record; >> cy integer := EXTRACT (YEAR FROM current_date); >> >> BEGIN >> >> FOR w IN >> SELECT (CASE WHEN m > 12 THEN cy + 1 ELSE cy END)::integer, (CASE >> WHEN m > 12 THEN m - 12 ELSE m END)::integer >> FROM generate_series(mon + 1, mon + intv) AS m >> LOOP >> RETURN next; >> END LOOP; >> >> END; >> >> $BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> >> >> SELECT * FROM month_year(10, 5); >> >> Why does it return empty SET ? The amount of rows is correct though .... >> I'm running 8.1.4 >> >> regards >> mk >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > Hi > > merlin=# CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer) > RETURNS SETOF record AS > $BODY$ > DECLARE > w record; > cy integer := EXTRACT (YEAR FROM current_date); > BEGIN > FOR w IN > SELECT (CASE WHEN m > 12 THEN cy + 1 ELSE cy END)::integer, (CASE > WHEN m > 12 THEN m - 12 ELSE m END)::integer > FROM generate_series(mon + 1, mon + intv) AS m > LOOP > RETURN next w; > END LOOP; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > and > > merlin=# SELECT * FROM month_year(10, 5) as (x integer, y integer); > x | y > ------+---- > 2008 | 11 > 2008 | 12 > 2009 | 1 > 2009 | 2 > 2009 | 3 > (5 rows) > > > without output params > > > -- > -- > Serdecznie pozdrawiam > > Pawel Socha > pawel.socha@gmail.com > > programista/administrator > > perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{ > a%%s%%$_%ee' >
"Pawel Socha" <pawel.socha@gmail.com> writes:
> 2008/8/4 Marcin Krawczyk <jankes.mk@gmail.com>
>> Hi everybody. Can anyone enlighten me what's wrong with this function :
> [ you didn't do "RETURN next w;" ]
IIRC the other way is to assign to the output parameters by name, then
do RETURN NEXT with no argument. But the FOR-loop all by itself isn't
going to return any data to the function's caller.
regards, tom lane