CODEWITHSUNDEEP
postgresqlplpgsql
user1533145
user1533145

Reputation: 33

How to get the last inserted IDs when there are multiple rows inserted in PL/pgSQL

I know I can use returning to get the last inserted ID, but what if the insert query inserts multiple rows at the same time and I want to get all the inserted IDs?

insert into tbl1
select * from tbl2 
returning tbl1.row_id into r_id

What do I need to do to so r_id would contain all the row_id of the inserted rows?

Upvotes: 1

Views: 75

Answers (1)

user330315
user330315

Reputation:

You can process them in a loop.

Something like this:

....
declare
  idrec record;
begin

  for idrec in insert into t1 select * from t2 returning row_id
  loop
     -- use idrec.row_id somehow
  end loop;
end;

Another option is to collect them into an array:

declare
   ids integer[];
begin
  with inserted (row_id) as (
     insert into t1 
     select * from t2 
     returning row_id
  )
  select array_agg(row_id)
    into ids
  from inserted;

  -- use ids array somehow
end;

Upvotes: 1

Related Questions