CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Rufi
Rufi

Reputation: 2645

How to get the first id from the INSERT query

Let's imagine that we have a plpgsql (PostgreSQL 10.7) function where there is a query like

INSERT INTO "myTable"
SELECT * FROM "anotherTable"
INNER JOIN "otherTable" 
...

So, this query will insert several rows into myTable. In the next query I want to collect the ids which were inserted with some condition. So, my idea was to do it the following:

INSERT INTO "resultTable" rt
SELECT FROM "myTable"
INNER JOIN ...
WHERE rt."id" >= firstInsertedId;

Now the question: how to find this firstInsertedId?

My solution:

select nextval(''"myTable.myTable_id_seq"'') into firstInsertedId;
if firstInsertedId > 1 then
    perform setval(''"myTable.myTable_id_seq"'', (firstInsertedId - 1));
end if;

I don't really like the solution as I don't think that it is good for the performance to generate the id, then go back, then generate it again during the insertion.

Thoughts:

Any suggestions?

Upvotes: 0

Views: 177

Answers (1)

user330315
user330315

Reputation:

You can do both things in a single statement using a data modifying common table expression. You don't really need PL/pgSQL for that.

with new_rows as (
  INSERT INTO my_table
  SELECT * 
  FROM anotherTable
    JOIN "otherTable" ...
  returning my_table.id
)
insert into resulttable (new_id)
select id 
from new_rows;

Another option would be to store the generate IDs in an array. 

declare
  l_ids integer[];
begin

  ....

  with new_rows as (
    INSERT INTO my_table
    SELECT * 
    FROM anotherTable
      JOIN "otherTable" ...
    returning my_table.id
  )
  select array_agg(id)
    into l_ids
  from new_rows;

  ....

end;

Upvotes: 3

Related Questions