PostgreSQL：嵌套插入

Question

我有两个表，假设为tblA和tblB。
我需要在tblA中插入一行，并将返回的id值用作插入到tblB中的一个列的值。

我尝试在文档中查找此信息，但未找到。那么，是否可以编写一个语句（用于预处理），如下：

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

我应该像SELECT语句一样处理吗？

还是说我需要创建一个存储过程？我不确定是否能够从存储过程中创建一个预处理语句。

请给予建议。

谢谢，
Mayank

- Mayank

Answer 1

你需要等待 PostgreSQL 9.1 版本才能实现这个功能:

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

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

- Denis de Bernardy

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。 编辑 另一种方法是分两步进行，先使用返回子句运行将数据插入到表 A 中，通过 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();

- user330315

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文档中可以了解到：

currval

返回当前会话中此序列最近一次nextval获取的值。（如果在此会话中从未为此序列调用过nextval，则报告错误。）因为这返回的是一个会话本地值，所以无论当前会话是否执行了nextval，它都会给出可预测的答案。

- Wayne Conrad

---

也许我有点偏执，但是依赖于 currval 不是最好的选择。首先，它假定 id 是基于序列的。其次，除非明确使用事务，否则键的值在插入之间可能会发生更改，这会破坏完整性。第三，它不能重复使用。但这只是我的观点，您不必同意 :) - Miki

@Sorrow，我已经编辑了答案，以解决你对键值改变的顾虑。对于其他指控，我不予辩护。 - Wayne Conrad

@Waynce，你的编辑澄清了问题。因此，我的评论现在应该声明“如果id绑定到序列，则依赖于currval是一个很好的选择”。然而，第三个关注点仍然存在-但它是一个设计问题，不包含在问题中，可能会引发无休止的辩论，所以最好在这里停止。 - Miki

@Sorrow，我同意，它完全不可移植。无论如何，感谢您帮助改善答案。 - Wayne Conrad

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 的风险是不存在的。

- Miki