Re: [SQL] return setof record - strange behavior

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'