Postgresql 中文操作指南

6.4. Returning Data from Modified Rows #

有时获取修改行中的数据很有用，而这些数据正在被处理。INSERT、UPDATE 和 DELETE 命令都有一个可选的 RETURNING 子句来支持此功能。使用 RETURNING 避免执行额外的数据库查询来收集数据，尤其当无法可靠地识别已修改的行时，这尤其有价值。

Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE, and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

RETURNING 条款允许的内容与 SELECT 命令的输出列表相同（请参见 Section 7.3）。它可以包含命令目标表的列名，或使用这些列的值表达式。RETURNING * 是一个常用的简写，它按顺序选择目标表的全部列。

The allowed contents of a RETURNING clause are the same as a SELECT command’s output list (see Section 7.3). It can contain column names of the command’s target table, or value expressions using those columns. A common shorthand is RETURNING *, which selects all columns of the target table in order.

在 INSERT 中，可供 RETURNING 使用的数据是按插入时的行。在简单的插入中没有那么有用，因为它只会重复客户端提供的数据。但在依赖于计算默认值时却非常方便。例如，当使用 serial 列提供唯一标识符时， RETURNING 可以返回分配给新行的 ID：

In an INSERT, the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values. For example, when using a serial column to provide unique identifiers, RETURNING can return the ID assigned to a new row:

CREATE TABLE users (firstname text, lastname text, id serial primary key);

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

RETURNING 子句也可以与 INSERT …​ SELECT 一起使用。

The RETURNING clause is also very useful with INSERT …​ SELECT.

在 UPDATE 中，RETURNING 可用的数据是已修改行的新的内容。例如：

In an UPDATE, the data available to RETURNING is the new content of the modified row. For example:

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

在 DELETE 中，RETURNING 可用的数据是已删除行的内容。例如：

In a DELETE, the data available to RETURNING is the content of the deleted row. For example:

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

如果目标表上有触发器 ( Chapter 39)，那么 RETURNING 能够获取的数据就是触发器修改后的行。因此，检查由触发器计算得出的列是 RETURNING 的另一个常见用例。

If there are triggers (Chapter 39) on the target table, the data available to RETURNING is the row as modified by the triggers. Thus, inspecting columns computed by triggers is another common use-case for RETURNING.