PostgreSQL：ALTER TABLE后通过触发器自动更新新增列的实现问题

嘿，我来帮你梳理下这个问题，你的思路方向是对的，但触发器的写法存在几个关键问题导致没生效，咱们一步步来修正：

先看你现有代码的几个问题：

1. 触发时机与触发条件不匹配：你创建的是AFTER UPDATE ON mytable1的触发器，但新增列后没有对mytable1执行UPDATE操作，所以触发器根本没机会触发，初始值自然是空的。
2. 函数内查询逻辑错误：SELECT COUNT(*) INTO num FROM mytable1, mytable2 WHERE mytable1.field = mytable2.field GROUP BY mytable1.field会返回多行结果（每个不同的field对应一行），但INTO num只能接收单行值，这会直接抛出错误（你可以去数据库日志里查看到这个报错）。
3. 全表更新的不合理性：在行级触发器里执行全表UPDATE mytable1，不仅效率极低，还可能引发循环触发的问题。

我们需要分两步：先初始化新增列的初始值，再创建合理的触发器保证后续数据变化时自动更新。

1. 先新增列并初始化数据 #

首先执行ALTER TABLE添加列，然后直接用UPDATE语句填充所有行的初始计数：

-- 新增列
ALTER TABLE mytable1 ADD new_column INT;

-- 初始化现有数据的匹配计数
UPDATE mytable1 t1
SET new_column = (
    SELECT COUNT(*)
    FROM mytable2 t2
    WHERE t2.field = t1.field
);

2. 创建正确的触发器函数与触发器 #

因为new_column的值依赖mytable1和mytable2两张表的field字段变化，所以我们需要给两张表都创建触发器，保证任何一边的数据变化都能同步更新计数：

触发器函数 #

这个函数会根据触发的表，针对性地更新关联行的计数：

CREATE OR REPLACE FUNCTION update_new_column()
RETURNS TRIGGER AS $BODY$
BEGIN
    -- 处理mytable1的行变化：更新当前行的计数
    IF TG_TABLE_NAME = 'mytable1' THEN
        UPDATE mytable1
        SET new_column = (
            SELECT COUNT(*) FROM mytable2 t2 WHERE t2.field = NEW.field
        )
        WHERE field = NEW.field;
        
        -- 如果是删除操作，还要处理旧值对应的计数？不，删除mytable1行的话该行直接消失，无需额外处理
        RETURN NEW;
    -- 处理mytable2的行变化：更新关联的mytable1行
    ELSIF TG_TABLE_NAME = 'mytable2' THEN
        -- 更新新field值对应的mytable1行
        UPDATE mytable1
        SET new_column = (
            SELECT COUNT(*) FROM mytable2 t2 WHERE t2.field = NEW.field
        )
        WHERE field = NEW.field;
        
        -- 如果是修改field值的情况，还要更新旧field值对应的mytable1行
        IF OLD.field IS NOT NULL AND OLD.field != NEW.field THEN
            UPDATE mytable1
            SET new_column = (
                SELECT COUNT(*) FROM mytable2 t2 WHERE t2.field = OLD.field
            )
            WHERE field = OLD.field;
        END IF;
        RETURN NEW;
    END IF;
END;
$BODY$ LANGUAGE PLPGSQL;

创建触发器 #

给两张表分别绑定触发器，覆盖INSERT/UPDATE/DELETE场景：

-- mytable1的field字段新增/修改/删除时触发
CREATE TRIGGER trigger_mytable1_sync_count
AFTER INSERT OR UPDATE OF field OR DELETE ON mytable1
FOR EACH ROW EXECUTE FUNCTION update_new_column();

-- mytable2的field字段新增/修改/删除时触发
CREATE TRIGGER trigger_mytable2_sync_count
AFTER INSERT OR UPDATE OF field OR DELETE ON mytable2
FOR EACH ROW EXECUTE FUNCTION update_new_column();

如果你不需要物理存储这个计数，其实可以考虑用视图来实时计算，这样就不用维护触发器了，比如：

CREATE VIEW mytable1_with_count AS
SELECT t1.*, (SELECT COUNT(*) FROM mytable2 t2 WHERE t2.field = t1.field) AS new_column
FROM mytable1 t1;

但如果必须把计数存在物理列里，上面的触发器方案就是最合适的。

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