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

On Sat, Sep 18, 2010 at 06:43:49PM -0700, Bret Green wrote:

how can I do the following in plpgsql?
insert multiple rows in a table
get the ids (serial) into a temp table (not the client)

for temprec in insert into table (x) select y from z returning id loop
insert into temp teable (q) values (temprec.id);
end loop;

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

On 19 Sep 2010, at 3:43, Bret Green wrote:

how can I do the following in plpgsql?

insert multiple rows in a table

get the ids (serial) into a temp table (not the client)

for one row it will be like this

insert into mytable(mycolumn)values(123)returning id into some_variable;

You can do that for multiple rows just fine, you don't even need plpgsql for that:

development=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
i | integer |

development=> INSERT INTO test (i) select x from generate_series(4, 10) t(x) ret
urning i;
i
----
4
5
6
7
8
9
10
(7 rows)

INSERT 0 7

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c96398f10255076983698!

No I do need it inside pgplsql. I need pgplsql for other stuff.

________________________________
From: Alban Hertroys <dalroi@solfertje.student.utwente.nl>
To: Bret Green <bret.green@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Sun, September 19, 2010 9:25:46 AM
Subject: Re: [GENERAL] how to insert multiple rows and get the ids back in a
temp table (pgplsql)?

On 19 Sep 2010, at 3:43, Bret Green wrote:

how can I do the following in plpgsql?

insert multiple rows in a table

get the ids (serial) into a temp table (not the client)

for one row it will be like this

insert into mytable(mycolumn)values(123)returning id into some_variable;

You can do that for multiple rows just fine, you don't even need plpgsql for
that:

development=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
i | integer |

development=> INSERT INTO test (i) select x from generate_series(4, 10) t(x) ret
urning i;
i
----
4
5
6
7
8
9
10
(7 rows)

INSERT 0 7

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:1170,4c96399610251136218112!

Thanks
Any solution without cursors perhaps?

________________________________
From: hubert depesz lubaczewski <depesz@depesz.com>
To: Bret Green <bret.green@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Sun, September 19, 2010 7:12:51 AM
Subject: Re: [GENERAL] how to insert multiple rows and get the ids back in a
temp table (pgplsql)?

On Sat, Sep 18, 2010 at 06:43:49PM -0700, Bret Green wrote:

how can I do the following in plpgsql?
insert multiple rows in a table
get the ids (serial) into a temp table (not the client)

for temprec in insert into table (x) select y from z returning id loop
insert into temp teable (q) values (temprec.id);
end loop;

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

On Sun, Sep 19, 2010 at 05:34:54PM -0700, Bret Green wrote:

Thanks
Any solution without cursors perhaps?

write sql function as a wrapper around insert, and use it as a source
for 2nd insert.

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

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.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-insert-multiple-rows-and-get-the-ids-back-in-a-temp-table-pgplsql-tp2845274p4287708.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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