PostgreSQL自定义类型字段在BEFORE INSERT触发器中的赋值语法问题

核心原因 #

你遇到的语法错误有两个核心原因：

• PL/pgSQL 不支持直接对 NEW 记录中复合类型字段的子属性做链式赋值，必须先将复合类型值取出到临时变量，修改子属性后再整体赋值回 NEW 的对应字段
• NULL 值判断不能用 = null，必须使用 IS NULL 运算符，因为 PostgreSQL 中任何值与 NULL 做等值比较的结果都是未知，永远不会触发条件判断

正确的触发器函数写法 #

CREATE OR REPLACE FUNCTION public.device_hours_data_trigger()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    v_hour_val public.hour_integer; -- 声明和自定义复合类型同类型的临时变量
BEGIN
    -- 处理h01字段的q_1为空的场景
    v_hour_val := NEW.h01;
    IF v_hour_val.q_1 IS NULL THEN
        v_hour_val.q_1 := 0;
        NEW.h01 := v_hour_val;
    END IF;

    -- 其他h02、h03等字段的处理逻辑参照以上写法即可，示例：
    -- v_hour_val := NEW.h02;
    -- IF v_hour_val.q_1 IS NULL THEN
    --     v_hour_val.q_1 := 0;
    --     NEW.h02 := v_hour_val;
    -- END IF;

    RETURN NEW;
END;
$BODY$;

绑定触发器到表 #

CREATE TRIGGER trg_device_hours_data_before_insert
BEFORE INSERT ON public.device_hours_data
FOR EACH ROW EXECUTE FUNCTION public.device_hours_data_trigger();

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