我可以在另一个INSERT语句中使用INSERT...RETURNING的返回值吗？

Question

我可以在另一个INSERT语句中使用INSERT...RETURNING的返回值吗？

125

这种情况可能吗？

INSERT INTO Table2 (val)
VALUES ((INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id));

比如使用返回值作为插入第二张表的一行的值，并且这一行与第一张表有关联？

- Eike Cochu

6个回答

87

在这种情况下，最佳实践是使用RETURNING … INTO。

INSERT INTO teams VALUES (...) RETURNING id INTO last_id;

请注意，这是针对PLPGSQL的。

- Alexandre Assi

8

这真的存在吗？你链接的文档似乎没有提到RETURNING ... INTO。 - alecbz

6

@ Alec：我在这个答案中找到了这份文档。 - Bart Hofland

@PedroD：是的。 - Bart Hofland

3

非常有用。如果其他读者不太清楚，您也可以使用 RETURNING col1, col2 INTO var1, var2。 - FlexYourData

@alecbz 哇，终于找到了。我刚刚花了过去3个小时寻找这个答案，应该将其标记为正确的答案。我只想将它存储在一个变量中以便重复使用。整个 WITH 子句对于我尝试实现的目标来说过于复杂。 - rolling_codes

23

DO $$
DECLARE tableId integer;
BEGIN
  INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id INTO tableId;
  INSERT INTO Table2 (val) VALUES (tableId);
END $$;

使用psql进行测试（版本为10.3，服务器版本为9.6.8）

- Anders B

19

与Denis de Bernardy所给的答案一致...

如果您希望id在之后返回，且想将更多内容插入Table2：

with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val, val2, val3)
SELECT id, 'val2value', 'val3value'
FROM rows
RETURNING val

- Bhindi

11

你可以使用lastval()函数:

返回任何序列最近使用nextval获得的值

所以像这样：

INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val)  VALUES (lastval());

只要在您的 INSERT 之间没有其他序列（在当前会话中）调用 nextval()，这将正常工作。

Denis 在下面指出，我在上面警告过，使用 lastval() 可能会让您陷入麻烦，如果另一个序列使用 nextval() 访问，则可能会发生这种情况在您的 INSERT 之间。如果在 Table1 上有一个 INSERT 触发器，该触发器手动调用了序列上的 nextval() 或者更可能在具有 SERIAL or BIGSERIAL 主键的表上进行了 INSERT。如果您想变得真正偏执（一件好事，毕竟他们确实是在追求您），那么您可以使用 currval()，但您需要知道相关序列的名称：

INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val)  VALUES (currval('Table1_id_seq'::regclass));

自动生成的序列通常被命名为t_c_seq，其中t是表名，c是列名，但您可以通过进入psql并输入以下命令来查找：

=> \d table_name;

然后查看相关列的默认值，例如:

id | integer | not null default nextval('people_id_seq'::regclass)

FYI: lastval()是PostgreSQL版本的MySQL的LAST_INSERT_ID。我之所以提到这一点，是因为很多人比较熟悉MySQL而不是PostgreSQL，因此将lastval()与熟悉的东西联系起来可能会澄清问题。

- mu is too short

2

最好使用currval()，因为如果table1上有触发器进行后续插入操作。 - Denis de Bernardy

@Denis：没错，但是你需要序列的名称。我会添加一些更新来涵盖所有情况。 - mu is too short

LASTVAL()和CURRVAL()都适用于当前数据库连接，而不是其他连接。其他用户可以同时更新序列，但这不会改变您的结果。不要担心其他人，他们永远不会更改您使用LASTVAL和/或CURRVAL的结果。当使用没有事务的连接池时，根本无法使用LASTVAL和CURRVAL，那时候问题就出现了：您无法控制数据库连接。 - Frank Heikens

1

@Frank：是的，它们都是会话特定的，但lastval的问题在于，在Table1上的AFTER INSERT触发器后面可能会有一个基于序列的INSERT。那将在当前会话中，并且当您不希望时，可能会改变lastval()。 - mu is too short

2

table_ex

id 默认 nextval('table_id_seq'::regclass),

camp1 varchar

camp2 varchar

INSERT INTO table_ex(camp1,camp2) VALUES ('xxx','123') RETURNING id

- kemado77

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Denis de Bernardy · Accepted Answer

您可以从Postgres 9.1开始这样做：

with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val)
SELECT id
FROM rows

如果你仅对ID感兴趣，可以使用触发器来实现：

create function t1_ins_into_t2()
  returns trigger
as $$
begin
  insert into table2 (val) values (new.id);
  return new;
end;
$$ language plpgsql;

create trigger t1_ins_into_t2
  after insert on table1
for each row
execute procedure t1_ins_into_t2();