PL/pgSQL动态存储函数返回任意表列名实现可更新视图触发器

1. 入参为schema、表名的列名查询存储函数 #

你之前卡壳的核心问题是混淆了format()函数里%I（标识符转义）和%L（字面量转义）的用法：在information_schema做过滤时，schema名、表名是作为字符串值匹配，应该用%L；如果是拼接SQL里的表名、列名标识符，才用%I。
可直接使用的函数如下，按表原始列顺序返回所有列名：

CREATE OR REPLACE FUNCTION get_table_columns(p_table_schema text, p_table_name text)
RETURNS SETOF text AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT column_name 
     FROM information_schema.columns 
     WHERE table_schema = %L AND table_name = %L
     ORDER BY ordinal_position',
    p_table_schema, p_table_name
  );
END;
$$ LANGUAGE plpgsql STABLE;

调用示例：SELECT * FROM get_table_columns('public', 'projects');

2. 动态拼接逻辑修正 #

你之前写的测试DO块存在逻辑错误：循环查询的是目标表的列值，而非列名本身，会导致拼接结果完全不符合预期，同时没有处理列顺序、开头冗余逗号的问题。修正后的测试逻辑如下：

DO $$
DECLARE
  v_col text;
  v_table_schema text DEFAULT 'public';
  v_table_name text DEFAULT 'projects';
  v_new_cols text DEFAULT '';
BEGIN
  FOR v_col IN EXECUTE format(
    'SELECT column_name FROM information_schema.columns 
     WHERE table_schema = %L AND table_name = %L
     ORDER BY ordinal_position',
    v_table_schema, v_table_name
  )
  LOOP
    v_new_cols := v_new_cols || ',NEW.' || quote_ident(v_col);
  END LOOP;
  -- 移除开头多余的逗号
  v_new_cols := ltrim(v_new_cols, ',');
  RAISE NOTICE '拼接后的NEW列片段: %', v_new_cols;
END$$;

3. 通用视图DML触发器实现 #

基于上述列名获取逻辑，可以直接写通用的INSTEAD OF触发器函数，不需要为每个视图单独编写DML逻辑，触发器接收目标父表的schema、表名作为入参即可：

CREATE OR REPLACE FUNCTION generic_view_dml_trigger()
RETURNS trigger AS $$
DECLARE
  v_target_schema text := TG_ARGV[0];
  v_target_table text := TG_ARGV[1];
  v_col_list text;
  v_new_val_list text;
  v_update_set text;
  v_pk_col text;
BEGIN
  -- 拼接列清单、NEW值清单
  SELECT string_agg(quote_ident(column_name), ','),
         string_agg('NEW.' || quote_ident(column_name), ',')
  INTO v_col_list, v_new_val_list
  FROM information_schema.columns
  WHERE table_schema = v_target_schema
    AND table_name = v_target_table
  ORDER BY ordinal_position;

  -- 获取目标表主键列（默认取第一个主键字段，多主键、外键逻辑可在此扩展）
  SELECT a.attname INTO v_pk_col
  FROM pg_index i
  JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = i.indkey[0]
  WHERE i.indrelid = format('%I.%I', v_target_schema, v_target_table)::regclass
    AND i.indisprimary;

  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE format(
        'INSERT INTO %I.%I (%s) VALUES (%s)',
        v_target_schema, v_target_table, v_col_list, v_new_val_list
      );
      RETURN NEW;
    WHEN 'UPDATE' THEN
      -- 拼接UPDATE的SET子句，默认跳过主键列
      SELECT string_agg(quote_ident(column_name) || ' = NEW.' || quote_ident(column_name), ',')
      INTO v_update_set
      FROM information_schema.columns
      WHERE table_schema = v_target_schema
        AND table_name = v_target_table
        AND column_name != v_pk_col
      ORDER BY ordinal_position;

      EXECUTE format(
        'UPDATE %I.%I SET %s WHERE %I = OLD.%I',
        v_target_schema, v_target_table, v_update_set, v_pk_col, v_pk_col
      );
      RETURN NEW;
    WHEN 'DELETE' THEN
      EXECUTE format(
        'DELETE FROM %I.%I WHERE %I = OLD.%I',
        v_target_schema, v_target_table, v_pk_col, v_pk_col
      );
      RETURN NULL;
  END CASE;
END;
$$ LANGUAGE plpgsql;

给视图绑定触发器的方式如下，以关联projects表的视图v_projects为例：

CREATE TRIGGER trg_v_projects_dml
INSTEAD OF INSERT OR UPDATE OR DELETE ON v_projects
FOR EACH ROW EXECUTE FUNCTION generic_view_dml_trigger('public', 'projects');

4. 方案可行性说明 #

你这套技术方案完全可行，适配QGIS视图编辑需求需要注意几个要点：

• 视图必须包含目标父表的主键列，否则QGIS无法识别图层的唯一标识，会禁止编辑操作。
• 后续扩展外键关联逻辑时，需要注意多表DML的执行顺序：插入/更新时先操作主表再操作子表，删除时反向操作，避免触发外键约束错误。
• 所有标识符全程用quote_ident()或format()的%I转义，兼容含大小写、特殊字符的列名/表名，避免SQL语法错误。
• 单表视图不需要写触发器，PostgreSQL原生支持自动更新，只有多表关联视图才需要用上述自定义触发器实现跨表更新，刚好匹配你的使用场景。

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