PostgreSQL触发器求和返回NULL：枚举转整数问题求助

原代码存在几个核心问题，导致触发器执行后返回NULL值且累加逻辑失效：

• 冗余查询当前插入行的枚举值，完全没必要从表中重新查询，直接用NEW变量即可
• 未处理poste_hta_bt.pui_kva的NULL值，直接相加会导致结果为NULL
• SELECT INTO语句在无匹配code_pt时会抛出异常，破坏执行流程

修改后的基础版触发器函数（仅支持INSERT） #

CREATE OR REPLACE FUNCTION recap_transf() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    puiss_int smallint;
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 直接从NEW获取枚举值，转换为整数
        CASE NEW.puiss_kva
            WHEN '100' THEN puiss_int := 100;
            WHEN '125' THEN puiss_int := 125;
            WHEN '150' THEN puiss_int := 150;
            ELSE puiss_int := 0;
        END CASE;

        -- 用COALESCE处理NULL值，确保累加正常
        UPDATE poste_hta_bt 
        SET pui_kva = COALESCE(pui_kva, 0) + puiss_int 
        WHERE code_pt = NEW.code_pt;
        
        RETURN NULL;
    ELSE
        RAISE WARNING '其他操作触发: %, 时间: %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$$;

关键改进说明 #

1. 移除冗余查询：直接通过NEW.puiss_kva获取当前插入行的枚举值，无需再查询transfo_hta_bt表，提升执行效率
2. NULL值安全处理：使用COALESCE(pui_kva, 0)将pui_kva的NULL值转为0，避免累加后结果为NULL
3. 简化逻辑：用CASE语句替代多个IF-ELSIF，代码更简洁易维护
4. 避免执行异常：直接使用UPDATE语句，即使无匹配的code_pt也只会执行0行更新，不会抛出异常

扩展版：支持INSERT/UPDATE/DELETE操作 #

如果需要处理变压器的更新和删除操作，同步调整poste_hta_bt的累加值，可以使用以下版本：

CREATE OR REPLACE FUNCTION recap_transf() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    old_puiss_int smallint;
    new_puiss_int smallint;
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            CASE NEW.puiss_kva
                WHEN '100' THEN new_puiss_int := 100;
                WHEN '125' THEN new_puiss_int := 125;
                WHEN '150' THEN new_puiss_int := 150;
                ELSE new_puiss_int := 0;
            END CASE;

            UPDATE poste_hta_bt 
            SET pui_kva = COALESCE(pui_kva, 0) + new_puiss_int 
            WHERE code_pt = NEW.code_pt;
        
        WHEN 'UPDATE' THEN
            -- 计算旧值对应的整数
            CASE OLD.puiss_kva
                WHEN '100' THEN old_puiss_int := 100;
                WHEN '125' THEN old_puiss_int := 125;
                WHEN '150' THEN old_puiss_int := 150;
                ELSE old_puiss_int := 0;
            END CASE;
            -- 计算新值对应的整数
            CASE NEW.puiss_kva
                WHEN '100' THEN new_puiss_int := 100;
                WHEN '125' THEN new_puiss_int := 125;
                WHEN '150' THEN new_puiss_int := 150;
                ELSE new_puiss_int := 0;
            END CASE;

            UPDATE poste_hta_bt 
            SET pui_kva = COALESCE(pui_kva, 0) - old_puiss_int + new_puiss_int 
            WHERE code_pt = NEW.code_pt;
        
        WHEN 'DELETE' THEN
            CASE OLD.puiss_kva
                WHEN '100' THEN old_puiss_int := 100;
                WHEN '125' THEN old_puiss_int := 125;
                WHEN '150' THEN old_puiss_int := 150;
                ELSE old_puiss_int := 0;
            END CASE;

            UPDATE poste_hta_bt 
            SET pui_kva = COALESCE(pui_kva, 0) - old_puiss_int 
            WHERE code_pt = OLD.code_pt;
        ELSE
            RAISE WARNING '其他操作触发: %, 时间: %', TG_OP, now();
    END CASE;
    RETURN NULL;
END;
$$;

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