postgresql：嵌套插入

Question

我有两张桌子。让我们说 tblA 和 tblB。
我需要在 tblA 中插入一行，并使用返回的 id 作为要插入到 tblB 中的列之一的值。

我尝试在文档中找到这一点，但无法得到它。 那样编写一条语句（旨在在准备中使用）

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

那么，是否可以像我们为 SELECT 所做的

？或者我应该通过创建存储过程来做到这一点？我不确定是否可以从存储过程中创建准备好的语句。

请指教。

问候，
玛雅克

Answer 1

为此，您需要等待 PostgreSQL 9.1：

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

同时，您需要在应用程序中使用 plpgsql、临时表或一些额外的逻辑...

Answer 2

这在 9.0 和用于匿名块的新 DO 中是可能的：

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$;

这可以作为单个语句执行。我还没有尝试从中创建一个PreparedStatement。

编辑

另一种方法是简单地分两步进行，首先使用returning子句运行插入到tableA中，通过JDBC获取生成的值，然后触发第二次插入，如下所示：

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();

Answer 3

您可以在两次插入中执行此操作，使用 currval() 检索外键（前提是键是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;

结果：

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

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

以这种方式使用 currval 是否安全在交易之内或之外。来自 Postgresql 8.4 文档：

电流

返回最近的值
通过该序列的 nextval 获得
在本届会议上。 （一个错误是
如果 nextval 从未存在过则报告
在此调用此序列
会话。）因为这返回一个
会话本地值，它给出了
可预测的答案是否
其他会话已执行nextval
自当前会话以来。

Answer 4

您可能需要使用 AFTER INSERT 触发器来实现这一点。 需要类似于：

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 的内容，因为您需要有 id 来引用它。希望这有帮助。

编辑

触发器将在与触发它的命令相同的“块”中被调用，即使不使用事务 - 换句话说，它在某种程度上成为该命令的一部分 .. 因此，不存在在插入之间更改引用 id 的风险。