从修改的行返回数据

概述

在数据库操作中，我们经常需要在修改数据后立即获取修改后的结果。PostgreSQL 提供了 RETURNING 子句，允许在执行 INSERT、UPDATE、DELETE 和 MERGE 操作时直接返回相关数据，这样可以避免执行额外的查询操作。

💡 核心优势

减少网络往返：一次操作完成修改和查询
提高性能：避免额外的 SELECT 查询
数据一致性：确保返回的是实际修改的数据
简化代码：减少应用程序的复杂性

RETURNING 子句基础语法

RETURNING 子句的语法与 SELECT 语句的输出列表相同，可以包含：

表的列名
基于列值的表达式
通配符 * 选择所有列
聚合函数和窗口函数

sql

-- 基本语法结构
INSERT/UPDATE/DELETE/MERGE ... RETURNING 表达式 [, ...]

INSERT 语句中的 RETURNING

基础示例

让我们从一个基础的员工表开始：

sql

-- 创建示例表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10,2),
    department_id INTEGER
);

示例 1：返回自动生成的主键

问题陈述：插入新员工记录时，需要获取系统自动生成的 ID。

sql

-- 插入数据并返回生成的 ID
INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('张', '三', '[email protected]', 8000.00, 1)
RETURNING id;

输出结果：

 id
----
  1
(1 row)

分析过程：

SERIAL 类型的 id 字段自动生成唯一值
RETURNING id 返回刚插入记录的主键值
避免了额外的 SELECT LASTVAL() 或类似查询

示例 2：返回计算字段和默认值

问题陈述：插入员工时，需要确认默认值的应用情况和计算结果。

sql

-- 插入数据并返回多个字段
INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('李', '四', '[email protected]', 9500.00, 2)
RETURNING id, first_name || ' ' || last_name AS full_name, hire_date, salary * 12 AS annual_salary;

输出结果：

 id | full_name | hire_date  | annual_salary
----+-----------+------------+---------------
  2 | 李 四     | 2025-05-27 |    114000.00
(1 row)

分析过程：

返回了自动生成的 id
使用字符串连接创建 full_name
显示了默认的 hire_date 值
计算了年薪 annual_salary

批量插入示例

示例 3：INSERT ... SELECT 与 RETURNING

问题陈述：从临时表批量导入员工数据，需要获取所有新插入记录的信息。

sql

-- 创建临时数据表
CREATE TEMP TABLE new_employees (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10,2),
    department_id INTEGER
);

-- 插入测试数据
INSERT INTO new_employees VALUES
('王', '五', '[email protected]', 7500.00, 1),
('赵', '六', '[email protected]', 8200.00, 3),
('钱', '七', '[email protected]', 9000.00, 2);

-- 批量插入并返回结果
INSERT INTO employees (first_name, last_name, email, salary, department_id)
SELECT first_name, last_name, email, salary, department_id FROM new_employees
RETURNING id, first_name, last_name, department_id;

输出结果：

 id | first_name | last_name | department_id
----+------------+-----------+---------------
  3 | 王         | 五        |             1
  4 | 赵         | 六        |             3
  5 | 钱         | 七        |             2
(3 rows)

分析过程：

一次操作插入多条记录
RETURNING 返回所有插入记录的指定字段
可以用于日志记录或后续处理

UPDATE 语句中的 RETURNING

基础更新示例

示例 4：工资调整并返回更新结果

问题陈述：为特定部门的员工调整工资，需要查看调整后的结果。

sql

-- 更新部门1的员工工资，增加10%
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1
RETURNING id, first_name, last_name, salary AS new_salary, salary / 1.10 AS old_salary;

输出结果：

 id | first_name | last_name | new_salary | old_salary
----+------------+-----------+------------+------------
  1 | 张         | 三        |    8800.00 |    8000.00
  3 | 王         | 五        |    8250.00 |    7500.00
(2 rows)

分析过程：

显示了更新后的新工资
通过计算展示了原工资（salary / 1.10）
明确显示哪些员工受到了影响

示例 5：条件更新与状态跟踪

问题陈述：更新员工信息时需要记录更新时间和变更内容。

sql

-- 首先添加更新时间字段
ALTER TABLE employees ADD COLUMN last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 更新特定员工的邮箱并返回详细信息
UPDATE employees
SET email = '[email protected]',
    last_updated = CURRENT_TIMESTAMP
WHERE id = 1
RETURNING id,
          first_name || ' ' || last_name AS employee_name,
          email AS new_email,
          last_updated,
          'Email updated' AS change_type;

输出结果：

 id | employee_name | new_email                | last_updated        | change_type
----+---------------+--------------------------+---------------------+--------------
  1 | 张 三         | [email protected] | 2025-05-27 10:30:15 | Email updated
(1 row)

复杂更新场景

示例 6：基于子查询的更新

问题陈述：根据部门平均工资调整员工工资，使其达到部门平均水平。

sql

-- 创建部门表
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    avg_salary DECIMAL(10,2)
);

INSERT INTO departments VALUES
(1, '技术部', 8500.00),
(2, '销售部', 9200.00),
(3, '市场部', 8800.00);

-- 更新低于部门平均工资的员工
UPDATE employees e
SET salary = d.avg_salary
FROM departments d
WHERE e.department_id = d.id
AND e.salary < d.avg_salary
RETURNING e.id,
          e.first_name || ' ' || e.last_name AS employee_name,
          e.salary AS new_salary,
          d.name AS department,
          d.avg_salary AS department_avg;

输出结果：

 id | employee_name | new_salary | department | department_avg
----+---------------+------------+------------+----------------
  3 | 王 五         |    8500.00 | 技术部     |        8500.00
(1 row)

分析过程：

使用 JOIN 操作与部门表关联
只更新工资低于部门平均值的员工
返回更新详情和部门信息

DELETE 语句中的 RETURNING

基础删除示例

示例 7：删除记录并保留审计信息

问题陈述：删除离职员工记录，但需要保留删除的记录信息用于审计。

sql

-- 创建审计表
CREATE TABLE deleted_employees (
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    employee_data JSONB
);

-- 删除特定员工并将信息保存到审计表
WITH deleted_data AS (
    DELETE FROM employees
    WHERE id = 5
    RETURNING id, first_name, last_name, email, salary, department_id, hire_date
)
INSERT INTO deleted_employees (employee_data)
SELECT row_to_json(deleted_data)::jsonb FROM deleted_data
RETURNING deleted_at, employee_data;

输出结果：

     deleted_at      |                                    employee_data
---------------------+--------------------------------------------------------------------------------------
 2025-05-27 10:35:20 | {"id": 5, "email": "[email protected]", "salary": 9000.00, "hire_date": "2025-05-27", "last_name": "七", "first_name": "钱", "department_id": 2}
(1 row)

示例 8：条件删除与统计

问题陈述：删除试用期未通过的员工，并统计删除情况。

sql

-- 删除试用期超过90天且工资较低的员工
DELETE FROM employees
WHERE hire_date < CURRENT_DATE - INTERVAL '90 days'
AND salary < 8000
RETURNING id,
          first_name || ' ' || last_name AS deleted_employee,
          salary,
          CURRENT_DATE - hire_date AS days_employed,
          'Trial period not passed' AS reason;

输出结果：

 id | deleted_employee | salary | days_employed | reason
----+------------------+--------+---------------+-----------------------
  3 | 王 五            | 8500.00|            90 | Trial period not passed
(1 row)

MERGE 语句中的 RETURNING

MERGE 语句是 PostgreSQL 15+ 引入的功能，允许在单个语句中执行插入、更新或删除操作。

基础 MERGE 示例

示例 9：员工信息同步

问题陈述：从外部系统同步员工信息，存在则更新，不存在则插入。

sql

-- 创建外部数据表
CREATE TEMP TABLE external_employees (
    employee_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10,2),
    department_id INTEGER
);

-- 插入外部数据
INSERT INTO external_employees VALUES
(1, '张', '三', '[email protected]', 8500.00, 1),  -- 更新现有
(6, '孙', '八', '[email protected]', 7800.00, 2);            -- 插入新的

-- 执行MERGE操作
MERGE INTO employees e
USING external_employees ext ON e.id = ext.employee_id
WHEN MATCHED THEN
    UPDATE SET
        email = ext.email,
        salary = ext.salary,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (id, first_name, last_name, email, salary, department_id)
    VALUES (ext.employee_id, ext.first_name, ext.last_name, ext.email, ext.salary, ext.department_id)
RETURNING
    CASE
        WHEN e.id IS NULL THEN 'INSERT'
        ELSE 'UPDATE'
    END AS operation,
    COALESCE(e.id, ext.employee_id) AS employee_id,
    ext.first_name || ' ' || ext.last_name AS employee_name,
    ext.email,
    ext.salary;

输出结果：

 operation | employee_id | employee_name | email                        | salary
-----------+-------------+---------------+------------------------------+---------
 UPDATE    |           1 | 张 三         | [email protected] |  8500.00
 INSERT    |           6 | 孙 八         | [email protected]           |  7800.00
(2 rows)

分析过程：

CASE 语句区分操作类型（INSERT 或 UPDATE）
COALESCE 确保返回正确的员工 ID
显示所有被影响的记录及其操作类型

高级应用场景

使用 RETURNING 进行数据流水线

示例 10：多表级联操作

问题陈述：创建员工账户时，需要同时在多个相关表中插入数据。

sql

-- 创建相关表
CREATE TABLE user_accounts (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    username VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employee_permissions (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    permission_level VARCHAR(20),
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 使用CTE和RETURNING进行级联插入
WITH new_employee AS (
    INSERT INTO employees (first_name, last_name, email, salary, department_id)
    VALUES ('陈', '九', '[email protected]', 8300.00, 2)
    RETURNING id, first_name, last_name
),
new_account AS (
    INSERT INTO user_accounts (employee_id, username)
    SELECT id, lower(first_name || '.' || last_name) FROM new_employee
    RETURNING employee_id, username, created_at
)
INSERT INTO employee_permissions (employee_id, permission_level)
SELECT employee_id, 'standard' FROM new_account
RETURNING employee_id, permission_level, granted_at;

输出结果：

 employee_id | permission_level |     granted_at
-------------+------------------+---------------------
           7 | standard         | 2025-05-27 10:45:30
(1 row)

性能优化示例

示例 11：批量操作性能对比

让我们比较使用 RETURNING 和传统方法的性能差异：

sql

-- 传统方法（需要多次查询）
-- 1. INSERT 操作
-- 2. SELECT 获取插入的数据
-- 3. 可能需要额外的查询来获取计算字段

-- 使用RETURNING的优化方法
INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES
    ('员工', '一', '[email protected]', 7500.00, 1),
    ('员工', '二', '[email protected]', 8000.00, 2),
    ('员工', '三', '[email protected]', 8500.00, 3)
RETURNING
    id,
    first_name || ' ' || last_name AS full_name,
    email,
    salary,
    salary * 12 AS annual_salary,
    hire_date,
    'Active' AS status;

输出结果：

 id | full_name | email              | salary  | annual_salary | hire_date  | status
----+-----------+--------------------+---------+---------------+------------+--------
  8 | 员工 一   | [email protected]   | 7500.00 |     90000.00  | 2025-05-27 | Active
  9 | 员工 二   | [email protected]   | 8000.00 |     96000.00  | 2025-05-27 | Active
 10 | 员工 三   | [email protected]   | 8500.00 |    102000.00  | 2025-05-27 | Active
(3 rows)

实际应用场景

场景 1：API 响应优化

在 Web 应用程序中，RETURNING 特别有用：

场景 2：审计日志系统

sql

-- 创建审计表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    old_values JSONB,
    new_values JSONB,
    changed_by VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 使用触发器和RETURNING进行审计
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_values, new_values, changed_by)
        VALUES (
            'employees',
            'UPDATE',
            row_to_json(OLD)::jsonb,
            row_to_json(NEW)::jsonb,
            current_user
        );
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_audit_trigger
    AFTER UPDATE ON employees
    FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();

最佳实践和注意事项

性能考虑

⚠️ 性能注意事项

RETURNING 会增加网络传输的数据量
大量数据的 RETURNING * 可能影响性能
在高并发环境中谨慎使用复杂表达式

数据类型处理

sql

-- 注意数据类型转换和格式化
UPDATE employees
SET salary = salary * 1.15
WHERE department_id = 1
RETURNING
    id,
    first_name,
    last_name,
    ROUND(salary, 2) AS formatted_salary,  -- 四舍五入
    TO_CHAR(salary, 'FM999,999.00') AS currency_format,  -- 货币格式
    last_updated::DATE AS update_date;  -- 类型转换

事务处理

💡 事务最佳实践

RETURNING 的结果在事务提交前是可见的
在长事务中谨慎使用，避免锁定时间过长
结合 SAVEPOINT 使用可以提供更好的错误恢复

sql

BEGIN;
SAVEPOINT before_update;

UPDATE employees
SET salary = salary * 1.20
WHERE department_id = 1
RETURNING id, first_name, last_name, salary;

-- 如果需要回滚
-- ROLLBACK TO SAVEPOINT before_update;

COMMIT;

常见错误和解决方案

错误 1：RETURNING 中引用不存在的列

sql

-- ❌ 错误示例
UPDATE employees
SET salary = 8000
WHERE id = 1
RETURNING id, non_existent_column;  -- 列不存在

-- ✅ 正确示例
UPDATE employees
SET salary = 8000
WHERE id = 1
RETURNING id, salary, last_updated;

错误 2：在不支持的语句中使用 RETURNING

sql

-- ❌ 错误示例
CREATE TABLE test AS SELECT * FROM employees RETURNING *;  -- 不支持

-- ✅ 正确示例
INSERT INTO test SELECT * FROM employees RETURNING *;

错误 3：MERGE 中的 RETURNING 引用错误

sql

-- ❌ 错误示例 - 在MERGE中直接引用源表列
MERGE INTO employees e USING external_employees ext ON e.id = ext.employee_id
WHEN MATCHED THEN UPDATE SET salary = ext.salary
RETURNING ext.salary;  -- 错误：不能直接引用源表

-- ✅ 正确示例
MERGE INTO employees e USING external_employees ext ON e.id = ext.employee_id
WHEN MATCHED THEN UPDATE SET salary = ext.salary
RETURNING e.salary;  -- 正确：引用目标表

总结

RETURNING 子句是 PostgreSQL 中一个强大的功能，它能够：

提高性能：减少数据库往返次数
简化代码：一次操作完成修改和查询
确保一致性：返回实际修改的数据
支持复杂场景：配合 CTE、触发器等高级功能

通过合理使用 RETURNING 子句，可以显著提升应用程序的性能和代码的简洁性。在设计数据库操作时，应该优先考虑使用 RETURNING 来减少不必要的查询操作。

从修改的行返回数据 ​

概述 ​

RETURNING 子句基础语法 ​

INSERT 语句中的 RETURNING ​

基础示例 ​

示例 1：返回自动生成的主键 ​

示例 2：返回计算字段和默认值 ​

批量插入示例 ​

示例 3：INSERT ... SELECT 与 RETURNING ​

UPDATE 语句中的 RETURNING ​

基础更新示例 ​

示例 4：工资调整并返回更新结果 ​

示例 5：条件更新与状态跟踪 ​

复杂更新场景 ​

示例 6：基于子查询的更新 ​

DELETE 语句中的 RETURNING ​

基础删除示例 ​

示例 7：删除记录并保留审计信息 ​

示例 8：条件删除与统计 ​

MERGE 语句中的 RETURNING ​

基础 MERGE 示例 ​

示例 9：员工信息同步 ​

高级应用场景 ​

使用 RETURNING 进行数据流水线 ​

示例 10：多表级联操作 ​

性能优化示例 ​

示例 11：批量操作性能对比 ​

实际应用场景 ​

场景 1：API 响应优化 ​

场景 2：审计日志系统 ​

最佳实践和注意事项 ​

性能考虑 ​

数据类型处理 ​

事务处理 ​

常见错误和解决方案 ​

错误 1：RETURNING 中引用不存在的列 ​

错误 2：在不支持的语句中使用 RETURNING ​

错误 3：MERGE 中的 RETURNING 引用错误 ​

总结 ​

从修改的行返回数据

概述

RETURNING 子句基础语法

INSERT 语句中的 RETURNING

基础示例

示例 1：返回自动生成的主键

示例 2：返回计算字段和默认值

批量插入示例

示例 3：INSERT ... SELECT 与 RETURNING

UPDATE 语句中的 RETURNING

基础更新示例

示例 4：工资调整并返回更新结果

示例 5：条件更新与状态跟踪

复杂更新场景

示例 6：基于子查询的更新

DELETE 语句中的 RETURNING

基础删除示例

示例 7：删除记录并保留审计信息

示例 8：条件删除与统计

MERGE 语句中的 RETURNING

基础 MERGE 示例

示例 9：员工信息同步

高级应用场景

使用 RETURNING 进行数据流水线

示例 10：多表级联操作

性能优化示例

示例 11：批量操作性能对比

实际应用场景

场景 1：API 响应优化

场景 2：审计日志系统

最佳实践和注意事项

性能考虑

数据类型处理

事务处理

常见错误和解决方案

错误 1：RETURNING 中引用不存在的列

错误 2：在不支持的语句中使用 RETURNING

错误 3：MERGE 中的 RETURNING 引用错误

总结