Skip site navigation (1) Skip section navigation (2)

Re: Array from INSERT .. RETURNING in plpgsql?

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Erik Jones" <ejones(at)engineyard(dot)com>
Cc: "pgsql-sql(at)postgresql(dot)org List" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Array from INSERT .. RETURNING in plpgsql?
Date: 2008-10-07 07:54:52
Message-ID: 162867790810070054o6d6fc035p278945927a478bee@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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
>

In response to

pgsql-sql by date

Next:From: Steve MidgleyDate: 2008-10-08 00:16:39
Subject: Re: many-to-many relationship
Previous:From: Erik JonesDate: 2008-10-07 07:27:55
Subject: Array from INSERT .. RETURNING in plpgsql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group