PostgreSQL实现依赖其他表聚合结果的列自动更新方案

PostgreSQL 实现物理存储跨表聚合值、数据变更自动重算的标准方案是AFTER 行级触发器 + PL/pgSQL 触发器函数，可完全覆盖员工新增、薪资调整、部门调动、员工删除四类场景，无需业务层额外介入。 #

1. 编写触发器通用函数 #

该函数会自动识别数据操作类型，收集所有受影响的部门ID，去重后逐个重新计算对应部门的平均薪资，部门无员工时自动赋值为0：

CREATE OR REPLACE FUNCTION refresh_dept_avg_salary()
RETURNS TRIGGER AS $$
DECLARE
  target_dpids integer[] := ARRAY[]::integer[];
  curr_dpid integer;
BEGIN
  -- 收集受影响的部门ID
  CASE TG_OP
    WHEN 'INSERT' THEN
      target_dpids := array_append(target_dpids, NEW.dpID);
    WHEN 'DELETE' THEN
      target_dpids := array_append(target_dpids, OLD.dpID);
    WHEN 'UPDATE' THEN
      -- 薪资变动：原部门需重算
      IF NEW.salary IS DISTINCT FROM OLD.salary THEN
        target_dpids := array_append(target_dpids, OLD.dpID);
      END IF;
      -- 部门调动：原部门、新部门均需重算
      IF NEW.dpID IS DISTINCT FROM OLD.dpID THEN
        target_dpids := array_append(target_dpids, OLD.dpID);
        target_dpids := array_append(target_dpids, NEW.dpID);
      END IF;
  END CASE;

  -- 兼容PostgreSQL 12及以下版本的去重逻辑，13+高版本可直接替换为array_distinct(target_dpids)
  FOR curr_dpid IN
    SELECT DISTINCT unnest(target_dpids) AS dpid
  LOOP
    UPDATE department
    SET avg_salary = COALESCE(
      (SELECT AVG(salary) FROM employee WHERE dpID = curr_dpid),
      0
    )
    WHERE dpID = curr_dpid;
  END LOOP;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

注意：你提供的示例聚合查询中表名存在拼写错误，正确表名为employee而非empoyee。
条件判断用IS DISTINCT FROM而非<>，可以兼容字段为NULL的场景，避免判断失效。

2. 绑定employee表行级触发器 #

必须使用AFTER触发器：等employee表数据变更完成后再计算聚合值，才能保证结果准确，禁止使用BEFORE触发器。

CREATE TRIGGER trg_employee_change_refresh_dept_salary
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
EXECUTE FUNCTION refresh_dept_avg_salary();

3. 初始化历史数据 #

触发器创建完成后，执行一次全量更新校准现有部门的avg_salary值，避免历史数据不一致：

UPDATE department d
SET avg_salary = COALESCE(
  (SELECT AVG(e.salary) FROM employee e WHERE e.dpID = d.dpID),
  0
);

方案说明 #

• 所有触发场景均已覆盖：新增员工仅更新所属部门、删除员工仅更新原部门、调薪更新当前部门、跨部门调动同时更新新旧两个部门
• 去重逻辑避免同一部门在单次操作中被重复计算，批量操作性能更稳定
• 空部门通过COALESCE将AVG返回的NULL转换为0，符合需求规则
• 所有计算逻辑下沉到数据库层，无论通过什么客户端修改employee表数据，avg_salary都会自动更新

内容的提问来源于stack exchange，提问作者boschl2000