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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Midgley | 2008-10-08 00:16:39 | Re: many-to-many relationship |
Previous Message | Erik Jones | 2008-10-07 07:27:55 | Array from INSERT .. RETURNING in plpgsql? |