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

Array from INSERT .. RETURNING in plpgsql?

From: Erik Jones <ejones(at)engineyard(dot)com>
To: "pgsql-sql(at)postgresql(dot)org List" <pgsql-sql(at)postgresql(dot)org>
Subject: Array from INSERT .. RETURNING in plpgsql?
Date: 2008-10-07 07:27:55
Message-ID: (view raw or whole thread)
Lists: pgsql-sql
Ok, so the following works:

pagila=# select array(select s.i from generate_series(1, 10) s(i));
            (1 row)

but this doesn't:

pagila=# create or replace function testfun() returns void as $$
     vals int[];
     query text;
     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];
$$ language plpgsql;
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


pgsql-sql by date

Next:From: Pavel StehuleDate: 2008-10-07 07:54:52
Subject: Re: Array from INSERT .. RETURNING in plpgsql?
Previous:From: Harold A. Giménez Ch.Date: 2008-10-06 21:52:09
Subject: Re: pg_dump more than one table in one command?

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