Re: pl/pgsql and arrays[] - Mailing list pgsql-general

From Maxim Boguk
Subject Re: pl/pgsql and arrays[]
Date
Msg-id CAK-MWwSRMbcztFghmPh3pvkFojSpVVDYEiLYaLSUPBxU0XE-Pg@mail.gmail.com
Whole thread Raw
In response to Re: pl/pgsql and arrays[]  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: pl/pgsql and arrays[]
List pgsql-general


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.

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

pgsql-general by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: pl/pgsql and arrays[]
Next
From: Pavel Stehule
Date:
Subject: Re: pl/pgsql and arrays[]