Re: Re: how to insert multiple rows and get the ids back in a temp table (pgplsql)?

On Wed, Apr 6, 2011 at 11:12 PM, abhishek.itbhu2004
<abhishek.itbhu2004@gmail.com> wrote:
> I am still new to postgres. Can you please tell the exact syntax for this. I
> tried different things but was not able to retun the ids of the newly
> inserted rows.

in 9.1 with wCTE you will have a very direct way to do this. in 9.0
down, there is only one effective way to do this.  you have to wrap
your insert statement in a plain (sql, not plpgsql) function and have
it return results:

create function ins_foo() returns setof int as
$$
  insert into foo(v) select 1 from generate_series(1,3) v returning id;
$$ language sql;

That function can be called from plpgsql or from another query:
select array(select ins_foo());

merlin