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(at)engineyard(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>