Re: return setof record - strange behavior

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