PostgreSQL 的 RETURNING 子句返回操作后的数据

sql -- 传统方式：插入后需要再查询 INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); -- 然后需要 SELECT 来获取生成的 id 等字段

-- 使用 RETURNING：一步完成 INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com') RETURNING *;

## 基础语法与适用语句

`RETURNING` 子句可以直接跟在 `INSERT`、`UPDATE` 或 `DELETE` 语句的末尾，支持返回任意表达式，如列名、`*`、常量或函数调用。

- **INSERT ... RETURNING**
- **UPDATE ... RETURNING**
- **DELETE ... RETURNING**

返回结果是一个结果集，就像执行了一条 `SELECT` 查询。

```sql
-- 返回特定列
INSERT INTO products (name, price) VALUES ('pen', 1.99)
RETURNING id, name, price;

-- 返回所有列
UPDATE products SET price = price * 1.1 WHERE id = 1
RETURNING *;

-- 返回表达式或别名
DELETE FROM products WHERE id = 1
RETURNING id, name, 'deleted' AS status;

典型应用场景

1. 获取自动生成的 ID 或默认值

当表中包含 SERIAL、IDENTITY 列或具有默认值的字段时，RETURNING 能立即获取数据库计算后的值，避免竞争条件。

-- 获取插入后生成的用户ID
INSERT INTO users (name) VALUES ('Bob')
RETURNING id;

2. 确认更新后的实际数据

UPDATE 可能基于现有值计算新值（如增加计数器），通过 RETURNING 可立刻得到更新后的最终状态，无需担心并发修改。

-- 增加库存并返回最新库存量
UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0
RETURNING product_id, quantity;

3. 安全删除并记录被删数据

在删除数据时，经常需要记录下被删除行的内容用于日志或回滚操作。RETURNING 让这一步变得简单。

-- 删除用户并保存被删行的信息到日志表
WITH deleted AS (
    DELETE FROM users WHERE status = 'inactive' RETURNING *
)
INSERT INTO user_deletion_log (user_id, deleted_at, email)
SELECT id, now(), email FROM deleted;

4. 与 CTE 结合实现复杂流程

RETURNING 的结果可以传入后续的 CTE（公用表表达式），实现流水线式的数据处理。

WITH new_order AS (
    INSERT INTO orders (customer_id) VALUES (123) RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity)
SELECT new_order.order_id, 99, 2 FROM new_order
RETURNING *;

5. 实现乐观锁更新并返回新旧值

当使用版本号实现乐观并发控制时，RETURNING 可一次性完成条件更新并返回最新版本号。

UPDATE documents
SET content = 'new content', version = version + 1
WHERE id = 10 AND version = 5
RETURNING id, version;
-- 如果返回空行，说明版本冲突，需要重试