PostgreSQL通用INSTEAD OF触发器：动态映射NEW字段拆分插入

核心思路 #

借助PostgreSQL的系统元数据（information_schema）和触发器内置变量，动态生成目标表名、匹配字段，实现一套触发器函数适配所有遵循「xxx视图对应xxx_static/xxx_version表」规则的场景，无需为每个视图编写硬编码的字段映射。

通用触发器函数实现 #

创建PL/pgSQL函数，自动完成以下操作：

• 从触发器上下文获取当前视图名，拼接出对应的xxx_static和xxx_version表名
• 查询系统表获取两张目标表的有效字段（排除系统列）
• 筛选出NEW行中与目标表字段匹配的列，动态构造INSERT语句执行

CREATE OR REPLACE FUNCTION generic_view_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
    static_table TEXT := TG_TABLE_NAME || '_static';
    version_table TEXT := TG_TABLE_NAME || '_version';
    static_cols TEXT;
    version_cols TEXT;
    static_vals TEXT;
    version_vals TEXT;
BEGIN
    -- 检查目标表是否存在
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = static_table) THEN
        RAISE EXCEPTION 'Static table % does not exist', static_table;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = version_table) THEN
        RAISE EXCEPTION 'Version table % does not exist', version_table;
    END IF;

    -- 生成static表的字段列表和对应NEW值
    SELECT string_agg(quote_ident(column_name), ', ')
    INTO static_cols
    FROM information_schema.columns
    WHERE table_name = static_table
      AND column_name NOT IN ('oid', 'ctid', 'xmin', 'xmax', 'cmin', 'cmax', 'tableoid')
      AND column_name = ANY (string_to_array(replace(NEW::TEXT, '(', ','), ',')::TEXT[]);

    SELECT string_agg('NEW.' || quote_ident(column_name), ', ')
    INTO static_vals
    FROM information_schema.columns
    WHERE table_name = static_table
      AND column_name NOT IN ('oid', 'ctid', 'xmin', 'xmax', 'cmin', 'cmax', 'tableoid')
      AND column_name = ANY (string_to_array(replace(NEW::TEXT, '(', ','), ',')::TEXT[]);

    -- 生成version表的字段列表和对应NEW值
    SELECT string_agg(quote_ident(column_name), ', ')
    INTO version_cols
    FROM information_schema.columns
    WHERE table_name = version_table
      AND column_name NOT IN ('oid', 'ctid', 'xmin', 'xmax', 'cmin', 'cmax', 'tableoid')
      AND column_name = ANY (string_to_array(replace(NEW::TEXT, '(', ','), ',')::TEXT[]);

    SELECT string_agg('NEW.' || quote_ident(column_name), ', ')
    INTO version_vals
    FROM information_schema.columns
    WHERE table_name = version_table
      AND column_name NOT IN ('oid', 'ctid', 'xmin', 'xmax', 'cmin', 'cmax', 'tableoid')
      AND column_name = ANY (string_to_array(replace(NEW::TEXT, '(', ','), ',')::TEXT[]);

    -- 执行static表插入
    IF static_cols IS NOT NULL THEN
        EXECUTE format('INSERT INTO %I (%s) VALUES (%s)', static_table, static_cols, static_vals);
    END IF;

    -- 执行version表插入
    IF version_cols IS NOT NULL THEN
        EXECUTE format('INSERT INTO %I (%s) VALUES (%s)', version_table, version_cols, version_vals);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

绑定触发器到视图 #

对每个需要的视图，创建INSTEAD OF INSERT触发器，直接调用上述通用函数即可：

-- 给usr视图绑定触发器
CREATE TRIGGER trg_usr_insert
INSTEAD OF INSERT ON usr
FOR EACH ROW EXECUTE FUNCTION generic_view_insert_trigger();

-- 如果有其他视图（如order），重复创建触发器即可
CREATE TRIGGER trg_order_insert
INSTEAD OF INSERT ON "order"
FOR EACH ROW EXECUTE FUNCTION generic_view_insert_trigger();

关键细节说明 #

• 动态表名：用TG_TABLE_NAME获取当前触发的视图名，自动拼接目标表名，无需硬编码
• 字段自动匹配：通过information_schema.columns查询目标表字段，同时匹配NEW行中存在的字段，省去手动列映射
• 安全防护：用quote_ident()处理字段名、format()格式化SQL，避免SQL注入风险
• 错误前置检查：先验证目标表是否存在，提前抛出明确错误

内容的提问来源于stack exchange，提问作者Marnix.hoh