PostgreSQL触发器中使用substring函数异常问题咨询

算力阿峰

2026-9-23

问题原因

语法错误：代码中set dummy #field_name的#field_name属于未替换的占位符，不符合PostgreSQL的SQL语法规范，执行时会直接报语法错误。
触发器逻辑冲突：你定义的是BEFORE INSERT OR UPDATE触发器，内部却执行同表UPDATE操作，存在两个致命问题：
- INSERT操作没有OLD系统变量，OLD.id为NULL，触发INSERT时WHERE条件无法匹配任何行，直接抛出空值错误
- 同表UPDATE会再次触发当前触发器，形成无限递归，最终导致事务栈溢出报错
字符串函数边界问题：当前逻辑未处理字段值过短、空值的场景，如果任意字段（比如city_name/phase_name等）为空或长度小于你截取的长度，right或substr可能返回NULL，最终拼接的整串会变为NULL。
触发器时机冗余：你的需求是自动给字段赋值生成编码，完全不需要额外执行UPDATE语句，BEFORE触发器直接修改NEW变量的值即可，原有UPDATE属于多余逻辑。

修复方案

直接改写触发器函数，去掉多余的同表UPDATE逻辑，直接给NEW对应的字段赋值即可，同时补充空值兼容处理，参考代码如下：

CREATE OR REPLACE FUNCTION TRIGGER1() RETURNS trigger AS $autouuid$
BEGIN
    -- 直接给NEW的dummy字段赋值，无需额外UPDATE
    NEW.dummy = upper(
        COALESCE(substr(NEW.city_name, 1, 2), '') || COALESCE(right(NEW.city_name, 1), '') || '-' ||
        COALESCE(substr(NEW.phase_name, 1, 2), '') || COALESCE(right(NEW.phase_name, 1), '') || '-' ||
        COALESCE(substr(NEW.area_name, 1, 3), '') || '-' ||
        COALESCE(substr(NEW.name, 1, 2), '') || COALESCE(right(NEW.name, 1), '') || '-' ||
        COALESCE(substr(NEW.rtu_model, 1, 2), '') || COALESCE(right(NEW.rtu_model, 1), '')
    );
    RETURN NEW;
END;
$autouuid$ LANGUAGE plpgsql;

-- 触发器定义可以保留，无需修改
CREATE TRIGGER autouuid_update BEFORE INSERT OR UPDATE ON test_points.scada_rtu
    FOR EACH ROW
EXECUTE PROCEDURE public.TRIGGER1();

如果你的需求是动态指定要更新的字段而非固定的dummy字段，需要使用EXECUTE执行动态SQL，注意要过滤INSERT场景避免OLD为空的问题，同时要给触发器加WHEN (pg_trigger_depth() < 1)的条件避免递归。

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