PostgreSQL 18新特性之DML语句RETURNING增强

PostgreSQL 很早就支持 DML 语句的 RETURNING 子句，用于返回插入、更正或者删除的数据。例如：

CREATE TABLE t(id int, v numeric);
INSERT INTO t(id, v) VALUES(1,1);
INSERT INTO t(id, v) VALUES(2,5);
INSERT INTO t(id, v) VALUES(3,10);

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

 id │  v
────┼─────
  1 │ 123
  2 │ 123
  3 │ 123
(3 ROWS)

以上示例中的 RETURNING 返回了被更新后的数据，但是无法返回被更新前的原始数据。

我们可以使用通用表表达（CTE）获取被更正前的数据，但是这种方法比较复杂难懂：

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)

PostgreSQL 18 已经提交了一个新的增强，可以支持在 INSERT、UPDATE、DELETE、MERGE 语句的 RETURNING 子句中使用特殊的别名 old 以及 new 返回被修改后或者修改前的数据。例如：

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)

其中，old 代表了被修改前的记录，new 代表了被修改后的记录。

对于 INSERT 语句，old 数据通常为 NULL；对于 DELETE 语句，new 数据通常为 NULL。但是对于 INSERT ON CONFLICT … DO UPDATE 语句，或者使用查询重写规则改变了命令类型时，old 数据可能不为 NULL。

另外，我们也可以为 old 或者 new 指定自定义的别名：

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

这个功能对于一些特殊场景非常有用，例如触发器函数（它们的定义也存在 old 和 new 关键字），或者使用了 old 或 new 关键字的历史代码。

来源：SQL编程思想