Thread: pl/pgsql and arrays[]

Hello

it work on my pc

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
 RETURNS SETOF integer
 LANGUAGE plpgsql
AS $function$ declare g int[] = '{20}';
begin
  return next g[1];
  return;
end;
$function$
postgres=# select fx();
 fx
----
 20
(1 row)

regards

Pavel Stehule

2011/12/5 Maxim Boguk <maxim.boguk@gmail.com>:
> Some quetions about pl/pgsql and arrays[].
>
> Is such constructions as:
>
> RETURN NEXT array[1];
>
> OR
>
> SELECT val INTO array[1] FROM ...;
>
> Should not work?
>
> At least documentation about RETURN NEXT  says:
> "RETURN NEXT expression;"
>
> I think array[1] is a valid expression.
>
> --
> Maxim Boguk
> Senior Postgresql DBA.

2011/12/5 Maxim Boguk <maxim.boguk@gmail.com>:
>
>
> On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> Hello
>>
>> it work on my pc
>>
>> postgres=# \sf fx
>> CREATE OR REPLACE FUNCTION public.fx()
>>  RETURNS SETOF integer
>>  LANGUAGE plpgsql
>> AS $function$ declare g int[] = '{20}';
>> begin
>>  return next g[1];
>>  return;
>> end;
>> $function$
>> postgres=# select fx();
>>  fx
>> ----
>>  20
>> (1 row)
>>
>> regards
>>
>> Pavel Stehule
>
>
> Oh sorry.
> Seems I didn't tested simple cases.
>

return next in function that returns composite type needs a composite
variable. Other cases are not supported there.

Regards

Pavel Stehule

> Error happened when you work with record[] types and return setof:
>
> create table test (id serial);
> insert into test select generate_series(1,10);
>
> CREATE OR REPLACE FUNCTION _test_array()
> RETURNS SETOF test
> LANGUAGE plpgsql
> AS $$
> DECLARE
>     _array test[];
>     _row   test%ROWTYPE;
> BEGIN
>      SELECT array(SELECT test FROM test) INTO _array;
>
>      --work
>      --_row := _array[1];
>      --RETURN NEXT _row;
>
>      --also work
>      --RETURN QUERY SELECT (_array[1]).*;
>
>      --error
>      --RETURN NEXT _array[1];
>
>      --error
>      --RETURN NEXT (_array[1]);
>
>      --error
>      --RETURN NEXT (_array[1]).*;
>
>      RETURN;
> END;
> $$;
>
>
>
>
>>
>>
>> 2011/12/5 Maxim Boguk <maxim.boguk@gmail.com>:
>> > Some quetions about pl/pgsql and arrays[].
>> >
>> > Is such constructions as:
>> >
>> > RETURN NEXT array[1];
>> >
>> > OR
>> >
>> > SELECT val INTO array[1] FROM ...;
>> >
>> > Should not work?
>> >
>> > At least documentation about RETURN NEXT  says:
>> > "RETURN NEXT expression;"
>> >
>> > I think array[1] is a valid expression.
>> >
>> > --
>> > Maxim Boguk
>> > Senior Postgresql DBA.
>
>
>
>
> --
> Maxim Boguk
> Senior Postgresql DBA.
>
> Phone RU: +7 910 405 4718
> Phone AU: +61 45 218 5678
>
> Skype: maxim.boguk
> Jabber: maxim.boguk@gmail.com
>
> LinkedIn profile: http://nz.linkedin.com/in/maximboguk
> If they can send one man to the moon... why can't they send them all?
>
> МойКруг: http://mboguk.moikrug.ru/
> Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
> все.

Pavel Stehule <pavel.stehule@gmail.com> writes:
> return next in function that returns composite type needs a composite
> variable. Other cases are not supported there.

Plain "return" has the same limitation, but this really ought to be
fixed sometime.  Composite types have been getting closer and closer
to first-class status since plpgsql was written.

            regards, tom lane

2011/12/5 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> return next in function that returns composite type needs a composite
>> variable. Other cases are not supported there.
>
> Plain "return" has the same limitation, but this really ought to be
> fixed sometime.  Composite types have been getting closer and closer
> to first-class status since plpgsql was written.

I sent this patch a few years ago - it was support for RETURN NEXT ROW(...)

but probably there was some issue

Regards

Pavel

>
>                        regards, tom lane