存储并重复使用INSERT ...返回的值 [英] Store and reuse value returned by INSERT ... RETURNING

问题描述

在PostgreSQL中，可以将 RETURNING 放在 INSERT 语句的末尾以返回，例如，当该行的主键值由 SERIAL 类型自动设置时。

In PostgreSQL, it is possible to put RETURNING at the end of an INSERT statement to return, say, the row's primary key value when that value is automatically set by a SERIAL type.

如何将此值存储在可用于将值插入其他表的变量中？
请注意，我想将生成的 id 插入到多个表中。据我了解， WITH 子句仅对单个插入有用。我认为这可能必须在PHP中完成。

How do I store this value in a variable that can be used to insert values into other tables? Note that I want to insert the generated id into multiple tables. A WITH clause is, as far as I understand, only useful for a single insert. I take it that this will probably have to be done in PHP.

这实际上是不良设计的结果；没有自然键，除非您对主键有一个句柄，否则很难抓住唯一的行；

This is really the result of bad design; without a natural key, it is difficult to grab a unique row unless you have a handle on the primary key;

推荐答案

...可以用来向其他表中插入值吗？

... that can be used to insert values into other tables?

您甚至可以在一个表中完成使用数据修改CTE 的SQL语句：

You can even do that in a single SQL statement using a data-modifying CTE:

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('foo')
   RETURNING tbl1_id
   )
INSERT INTO tbl2(tbl1_id)
SELECT * FROM ins1

需要PostgreSQL 9.1或更高版本。

Requires PostgreSQL 9.1 or later.

db<> fiddle 此处 （Postgres 11）

sqlfiddle （Postgr es 9.6）

db<>fiddle here (Postgres 11)
sqlfiddle (Postgres 9.6)

您还可以插入多个表在单个查询中：

You can also insert into multiple tables in a single query:

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('bar')
   RETURNING tbl1_id
   )
 , ins2 AS (
   INSERT INTO tbl2(tbl1_id)
   SELECT tbl1_id FROM ins1
   )
INSERT INTO tbl3(tbl1_id)
SELECT tbl1_id FROM ins1;

这篇关于存储并重复使用INSERT ...返回的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！