Re: return setof record - strange behavior - Mailing list pgsql-sql

<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>