Re: pl/pgsql and arrays[]

On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

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(dot)boguk(at)gmail(dot)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(dot)boguk(at)gmail(dot)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/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.