Thread: Array from INSERT .. RETURNING in plpgsql?

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

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
>