Can I use return value of INSERT...RETURNING in another INSERT?

Question

Is something like this possible?

INSERT INTO Table2 (val) VALUES ((INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id)); 

like using the return value as value to insert a row in a second table with a reference to the first table?

Answer 1

You can do so starting with Postgres 9.1:

with rows as ( INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id ) INSERT INTO Table2 (val) SELECT id FROM rows 

In the meanwhile, if you're only interested in the id, you can do so with a trigger:

create function t1_ins_into_t2()   returns trigger as $$ begin   insert into table2 (val) values (new.id);   return new; end; $$ language plpgsql;  create trigger t1_ins_into_t2   after insert on table1 for each row execute procedure t1_ins_into_t2(); 

Answer 2

The best practice for this situation. Use RETURNING … INTO.

INSERT INTO teams VALUES (...) RETURNING id INTO last_id; 

Note this is for PLPGSQL