postgresql: nested insert

I have two tables. Lets say tblA and tblB.

I need to insert a row in tblA an开发者_JAVA技巧d 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

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...

This is possible with 9.0 and the new DO for anonymous blocks:

do $$
declare 
  new_id integer;
begin
  insert into foo1 (id) values (default) returning id into new_id;
  insert into foo2 (id) values (new_id);
end$$;

This can be executed as a single statement. I haven't tried creating a PreparedStatement out of that though.

Edit

Another approach would be to simply do it in two steps, first run the insert into tableA using the returning clause, get the generated value through JDBC, then fire the second insert, something like this:

PreparedStatement stmt_1 = con.prepareStatement("INSERT INTO tblA VALUES (DEFAULT, ?) returning id");
stmt_1.setString(1, "x");
stmt_1.execute(); // important! Do not use executeUpdate()!
ResultSet rs = stmt_1.getResult();
long newId = -1;
if (rs.next()) {
   newId = rs.getLong(1);
}
PreparedStatement stmt_2 = con.prepareStatement("INSERT INTO tblB VALUES (default,?,?)");
stmt_2.setLong(1, newId);
stmt_2.setString(2, "y");
stmt_2.executeUpdate();

You can do this in two inserts, using currval() to retrieve the foreign key (provided that key is serial):

create temporary table tb1a (id serial primary key, t text);
create temporary table tb1b (id serial primary key,
                             tb1a_id int references tb1a(id),
                             t text);
begin;
insert into tb1a values (DEFAULT, 'x');
insert into tb1b values (DEFAULT, currval('tb1a_id_seq'), 'y');
commit;

The result:

select * from tb1a;
 id | t
----+---
  3 | x
(1 row)

select * from tb1b;
 id | tb1a_id | t
----+---------+---
  2 |       3 | y
(1 row)

Using currval in this way is safe whether in or outside of a transaction. From the Postgresql 8.4 documentation:

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

You may want to use AFTER INSERT trigger for that. Something along the lines of:

create function dostuff() returns trigger as $$
begin
 insert into table_b(field_1, field_2) values ('foo', NEW.id);
 return new; --values returned by after triggers are ignored, anyway
end;
$$ language 'plpgsql';

create trigger trdostuff after insert on table_name for each row execute procedure dostuff();

after insert is needed because you need to have the id to reference it. Hope this helps.

Edit

A trigger will be called in the same "block" as the command that triggered it, even if not using transactions - in other words, it becomes somewhat part of that command.. Therefore, there is no risk of something changing the referenced id between inserts.