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
>
