Обсуждение: 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!

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