INSERT new records and use resulting primary keys for another INSERT

Question

If I want to save the primary key (mytable_id) from a single insert I've done the following:

CREATE OR REPLACE FUNCTION myfunct(ownerid text) RETURNS void AS 
$$
DECLARE myvar INTEGER;

BEGIN

INSERT INTO mytable (long_description) VALUE 'testing';

SELECT CURRVAL('mytable_mytable_id_seq') INTO myvar;

END;
$$ 
LANGUAGE plpgsql;

The problem is, I now want to do the same BUT on multiple inserts. I want to insert a row, and take that newly inserted row's PK and use it as a foreign key in another insert.

So I have

DECLARE myvar2 INTEGER;

INSERT INTO mytable (long_description)
SELECT DISTINCT type FROM schema1.myothertable WHERE type IS NOT NULL;

INSERT INTO mytable2 (foreign_key1, foregin_key2)
VALUES (myvar, myvar2); -- somewhere pk from mytable is stored in myvar2

Is there a way to perform these two inserts in the same query?
I wanted to avoid loops but I'm not sure this can be done without it.

Look in the docs for the `RETURNING` keyword. – Jonathan Vanasco Jul 31 '14 at 18:15 — Jonathan Vanasco, Jul 31 '14 at 18:15

score 0 · Accepted Answer · answered Jul 31 '14 at 18:29

Sorry, if I did not get you right. But why not using nextval once? Postgres functions are always transactions, so no danger, if you do this:

CREATE OR REPLACE FUNCTION myfunct(ownerid text) RETURNS void AS 
$$
DECLARE 
     nextId INTEGER;
BEGIN
     SELECT nextval('mytable_mytable_id_seq') INTO nextId;
     INSERT INTO mytable (id, long_description) values (nextId, 'testing');
     INSERT INTO othertable (id, whatever) values (nextId, 'whatever');
 END;
 $$
 LANGUAGE plpgsql;

You might insert whatever and whereever you want with the very same pk/fk then.

Thank you!! I recently discovered nextval and currval. Much appreciated :) — user3688391, Aug 02 '14 at 12:38

score 0 · Answer 2 · edited May 23 '17 at 10:33

Use data-modifying CTEs to do it all in a single statement. That's fastest and cleanest. You can wrap in in a function if you want to ...

WITH ins AS (
   INSERT INTO mytable (long_description)
   SELECT DISTINCT type
   FROM   schema1.myothertable
   WHERE  type IS NOT NULL
   RETURNING *
   )
INSERT INTO mytable2 (foreign_key1, foregin_key2)
SELECT 'myvar', ins.mytable_id
FROM   ins
RETURNING *;            -- optional

More explanation in this related answer:

PostgreSQL multi INSERT...RETURNING with multiple columns

INSERT new records and use resulting primary keys for another INSERT

2 Answers2