Skip site navigation (1) Skip section navigation (2)

Re: return setof record - strange behavior

From: "Marcin Krawczyk" <jankes(dot)mk(at)gmail(dot)com>
To: "Pawel Socha" <pawel(dot)socha(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: return setof record - strange behavior
Date: 2008-08-04 11:33:59
Message-ID: 95f6bf9b0808040433p78f2eaebobb59a05ea67b6565@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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(dot)socha(at)gmail(dot)com>:
>
>
> 2008/8/4 Marcin Krawczyk <jankes(dot)mk(at)gmail(dot)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(at)postgresql(dot)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(dot)socha(at)gmail(dot)com
>
> programista/administrator
>
> perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
> a%%s%%$_%ee'
>

In response to

pgsql-sql by date

Next:From: Mike GouldDate: 2008-08-04 13:54:38
Subject: Case Insensitive searches
Previous:From: Pawel SochaDate: 2008-08-04 10:56:10
Subject: Re: return setof record - strange behavior

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group