[SQL] return setof record - strange behavior

2008-08-04 Thread Marcin Krawczyk 
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

[SQL] return setof record - strange behavior

2008-08-04 Thread Marcin Krawczyk 
The function behaves as expected when in plain SQL, only plpgsql
function has the above mentioned problem.

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

Re: [SQL] return setof record - strange behavior

2008-08-04 Thread Pawel Socha 
2008/8/4 Marcin Krawczyk [EMAIL PROTECTED]

 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
[EMAIL PROTECTED]

programista/administrator

perl -le 's**02).4^-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2**y% -;^[%`-{
a%%s%%$_%ee'

Re: [SQL] return setof record - strange behavior

2008-08-04 Thread Marcin Krawczyk 
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 [EMAIL PROTECTED]:


 2008/8/4 Marcin Krawczyk [EMAIL PROTECTED]

 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
 [EMAIL PROTECTED]

 programista/administrator

 perl -le 's**02).4^-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2**y% -;^[%`-{
 a%%s%%$_%ee'


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] return setof record - strange behavior

2008-08-04 Thread Tom Lane 
Pawel Socha [EMAIL PROTECTED] writes:
 2008/8/4 Marcin Krawczyk [EMAIL PROTECTED]
 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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql