Re: [SQL] 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'
>

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