Re: Array from INSERT .. RETURNING in plpgsql?

Hello

I afraid, it isn't possible. You cannot use returning in subqueries,
and returned value from RETURNING clause isn't array.

you can do

declare  _sa int[] = '{}';  _a int;
begin for a in execute 'insert .... returning i' loop   _sa := _sa || _a; end loop; return _sa;
end;

but this query will be slow for bigger returned arrays than 10000 fields
regards
Pavel Stehule
 query := 'insert into test select s.i from generate_series(1,10)
s(i) returning i;';

2008/10/7 Erik Jones <ejones@engineyard.com>:
> Ok, so the following works:
>
> pagila=# select array(select s.i from generate_series(1, 10) s(i));
>          ?column?
>  ------------------------
> {1,2,3,4,5,6,7,8,9,10}
>           (1 row)
>
> but this doesn't:
>
> pagila=# create or replace function testfun() returns void as $$
> declare
>    vals int[];
>    query text;
> begin
>    query := 'insert into test select s.i from generate_series(1,10) s(i)
> returning i;';
>    execute query into vals;
>    raise notice 'vals dim: %', array_upper(vals, 1);
>    raise notice 'vals[3]: %', vals[3];
> end;
> $$ language plpgsql;
> CREATE FUNCTION
> Time: 3.319 ms
> pagila=# select testfun();
> ERROR:  array value must start with "{" or dimension information
> CONTEXT:  PL/pgSQL function "testfun" line 6 at execute statement
>
> Is there any way to do what I'm trying without explicity looping over the
> results of the insert?
>
> Erik Jones, Database Administrator
> Engine Yard
> Support, Scalability, Reliability
> (415) 963-4410 x 260
> Location: US/Pacific
> IRC: mage2k
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>