postgresql: nested insert

Question

I have two tables. Lets say tblA and tblB.
I need to insert a row in tblA and use the returned id as a value to be inserted as one of the columns in tblB.

I tried finding out this in documentation but could not get it. Well, is it possible to write a statement (intended to be used in prepared) like

INSERT INTO tblB VALUES 
(DEFAULT, (INSERT INTO tblA (DEFAULT, 'x') RETURNING id), 'y')

like we do for SELECT?

Or should I do this by creating a Stored Procedure?. I'm not sure if I can create a prepared statement out of a Stored Procedure.

Please advise.

Regards,
Mayank

Answer 1

You'll need to wait for PostgreSQL 9.1 for this:

with
ids as (
insert ...
returning id
)
insert ...
from ids;

In the meanwhile, you need to use plpgsql, a temporary table, or some extra logic in your app...