PostgreSql INSERT FROM SELECT RETURNING ID

Question

In PostgreSql 9.2.4 I have two tables: user (id, login, password, name) and dealer (id, user_id).

And I want to insert into both tables returning id of created dealer.

Currently I'm doing it with two queries:

WITH rows AS (     INSERT INTO "user"         (login, password, name)     VALUES         ('dealer1', 'jygbjybk', 'Dealer 1')     RETURNING id ) INSERT INTO dealer (user_id)     SELECT id     FROM rows; SELECT currval('dealer_id_seq'); 

But can I implement this with a single INSERT query using RETURNING statement?

Answer 1

You just need to add a RETURNING id to your INSERT ... SELECT:

WITH rows AS (...) INSERT INTO dealer (user_id)     SELECT id     FROM rows     RETURNING id; 

Demo: http://sqlfiddle.com/#!12/75008/1

Answer 2

For my purposes, I needed it in a variable so I did this:

INSERT INTO dealer (user_id)     SELECT id     FROM rows     RETURNING id INTO l_dealerid;