PostgreSQL 18 预览: DML 查询 RETURNING 子句支持 OLD/NEW

由 John Doe 三月 24, 2025

你需要在 DML 查询中，返回更改前后的列值吗？现在，PostgreSQL 可以了。

一头与 SQL 共舞的大象

特性提交日志

在 DML 查询的 RETURNING 子句中增加 OLD/NEW 支持。

对于 INSERT/UPDATE/DELETE/MERGE 查询的 RETURNING 子句中的列表，这使得其能够通过使用特殊别名 “old” 和 “new” 显式返回旧值和新值。在解析 RETURNING 列表时，这些别名在尚未定义的情况下会自动添加到查询中，这允许以下的用法：

RETURNING old.colname, new.colname, ...

RETURNING old.*, new.*

此外，还支持一种新语法，允许将 “old” 和 “new” 这两个名称更改为用户提供的别名。例如：

RETURNING WITH (OLD AS o, NEW AS n) o.colname, n.colname, ...

当 “old” 和 “new” 这两个名称已经被定义时（比如在触发器函数中），这种语法就很有用，它可以保持向后兼容性：对于任何碰巧已经引用了名为 “old” 或 “new” 的关系，或者将其用作其他关系别名的现有的查询，它们的解释和行为不会发生改变。

对于 INSERT 操作，旧值通常为 NULL；对于 DELETE 操作，新值通常为 NULL。但是，对于带有ON CONFLICT...DO UPDATE子句的 INSERT 操作，或者如果查询重写规则改变了操作的类型，情况可能会有所不同。因此，PostgreSQL 18 对于任何 DML 查询使用 old 和 new 没有限制。

讨论：https://postgr.es/m/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com

示例

在 2006 年发布的 PostgreSQL 8.2 版本中，我们有了用于插入/更新/删除操作的 RETURNING 子句。虽然它很好用，但它不支持在更新操作中返回更改前的列值。

例如：

UPDATE t SET v = 123 WHERE id IN (1,2,3) RETURNING id, v;
 id |  v
----+-----
  1 | 123
  2 | 123
  3 | 123
(3 rows)

这个结果很不错，但在某些情况下，能够返回更新前的值会更好。

当然，我们可以使用 CTE（公共表表达式）来实现，先进行查询（使用FOR UPDATE），然后再进行更新，但这样做容易出错、繁琐且可读性较差：

WITH
    x AS (SELECT id, v FROM t WHERE id IN (1,2,3) FOR UPDATE),
    u AS (UPDATE t SET v = 123 FROM x WHERE t.id = x.id RETURNING t.id, t.v)
SELECT COALESCE(x.id, u.id), x.v AS before, u.v AS after
    FROM x FULL OUTER JOIN u USING (id);
 coalesce | before | after
----------+--------+-------
        1 |      1 |   123
        2 |      5 |   123
        3 |     10 |   123
(3 rows)

幸运的是，现在这个问题解决了。

我们可以这样做：

UPDATE t SET v = LEAST(123 * v, 200) WHERE id IN (1,2,3) RETURNING id, old.v, new.v;
 id | v  |  v
----+----+-----
  1 |  1 | 123
  2 |  5 | 200
  3 | 10 | 200
(3 rows)