28 RETURNING

从修改数据的语句中直接返回生成的标识符和已修改的行。

RETURNING 是 PostgreSQL 提供的扩展，它能把 INSERT、UPDATE 或 DELETE 变成查询：语句会返回刚刚处理过的行。这样就不必再进行第二次网络往返，去获取自动生成的 id、更新后的时间戳或刚刚删除的值。大多数专业代码库都高度依赖 RETURNING。

本章要点

RETURNING 与 INSERT、UPDATE、DELETE 配合使用
返回特定列与使用 RETURNING * 的区别
在一次调用中获取生成的值（标识列、默认值）
在公用表表达式（CTE，WITH ... AS (...)）中使用 RETURNING 串联操作
为什么它几乎总能替代“先 INSERT，再调用 SELECT last_insert_id()”

示例

示例 28.1 RETURNING

-- Setup
DROP TABLE IF EXISTS logs_example;
CREATE TABLE logs_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  msg  text NOT NULL,
  at   timestamptz NOT NULL DEFAULT current_timestamp
);

-- INSERT ... RETURNING: get the generated id and timestamp in one round trip
INSERT INTO logs_example (msg) VALUES ('first') RETURNING id, msg, at;
INSERT INTO logs_example (msg) VALUES ('second'), ('third') RETURNING id;

-- UPDATE ... RETURNING: see the new values of the modified rows
UPDATE logs_example
SET    msg = msg || ' (updated)'
WHERE  id = 1
RETURNING id, msg;

-- DELETE ... RETURNING: see what was removed
DELETE FROM logs_example
WHERE  id = 3
RETURNING *;

-- Chaining: insert into one table, then route the new ids into another
DROP TABLE IF EXISTS log_archive;
CREATE TABLE log_archive (id bigint PRIMARY KEY, msg text, archived_at timestamptz DEFAULT current_timestamp);

WITH moved AS (
  DELETE FROM logs_example
  WHERE id <= 2
  RETURNING id, msg
)
INSERT INTO log_archive (id, msg)
SELECT id, msg FROM moved
RETURNING id;

RETURNING 接受你可以放进 SELECT 列表中的同样表达式——列引用、表达式、函数调用，甚至 *。对于 INSERT，这些列反映插入后的值，包括默认值和生成的标识列值。对于 UPDATE，它们反映新值；在 PostgreSQL 17 中，OLD.col 可在触发器函数中使用，不能在 RETURNING 中使用。PostgreSQL 18 在 RETURNING 中加入了显式的旧值/新值。对于 DELETE，它们反映删除前的行。

串联模式（WITH del AS (DELETE ... RETURNING) INSERT INTO archive SELECT * FROM del）是 PostgreSQL 的强大之处之一。两条语句会在一次往返中作为一个原子操作执行——比在应用代码中完成同样的事情快得多，也简单得多。

运行示例

运行方式：

$ psql -f source/returning.sql postgres
DROP TABLE
CREATE TABLE
 id |  msg  |              at
----+-------+-------------------------------
  1 | first | 2024-xx-xx xx:xx:xx.xxxxxx+00
(1 row)
 id
----
  2
  3
(2 rows)

 id |       msg
----+-----------------
  1 | first (updated)
(1 row)
 ...

常见陷阱

在 INSERT 时忘记使用 RETURNING，然后执行 SELECT max(id) FROM t;——这会产生竞态条件。始终使用 RETURNING id 获取新行的 id。
多行 INSERT ... RETURNING 会按插入顺序返回行，但这只是因为执行器的处理方式如此。如果需要有保证的映射，请在 VALUES 和 RETURNING 中都包含一个稳定键。
INSERT ... ON CONFLICT DO NOTHING 中的 RETURNING 不会返回因冲突而跳过的行。请使用 DO UPDATE（编校说明：原文所说的“下一课”实际指第 25 章的 UPSERT）来确保返回某一行。
驱动程序有时会把 INSERT ... RETURNING 当作查询（带结果）提供，而不是当作语句（返回行数）提供。请检查相应语言的 API。

提示

INSERT INTO t (...) VALUES (...) RETURNING id 是“创建一行并告诉我它的 id”的标准写法。它适用于 bigint GENERATED ALWAYS AS IDENTITY、serial、UUID 默认值，或数据库提供的任何其他值。请在所有地方使用它。

动手练习

用一条语句插入三行，并通过 RETURNING id 获取这三个 id。然后运行 UPDATE logs_example SET msg = msg WHERE id > 0 RETURNING id, msg;，看看所有被触碰的行（“无操作”更新仍然会触碰一行）。然后使用 CTE 串联模式，将行从 logs_example 移到 log_archive。

28 RETURNING

本章要点

示例

运行示例

常见陷阱

下载