PostgreSQL 14如何全局设置文本字段自动Trim（无需触发器）

是的，你可以通过**事件触发器（Event Trigger）**结合通用触发器函数的方式，实现无需为每张表手动创建触发器的全局自动Trim效果，具体方案如下：

1. 创建通用的Trim处理函数 #

这个函数会遍历表中的所有文本类型字段（text、varchar、char），在插入/更新前对字段值执行TRIM()操作：

CREATE OR REPLACE FUNCTION trim_text_fields()
RETURNS TRIGGER AS $$
BEGIN
  -- 遍历所有文本类型的列
  FOR col IN SELECT attname
             FROM pg_attribute
             WHERE attrelid = TG_RELID
               AND attnum > 0
               AND NOT attisdropped
               AND atttypid IN (
                 'text'::regtype,
                 'varchar'::regtype,
                 'character varying'::regtype,
                 'char'::regtype,
                 'character'::regtype
               )
  LOOP
    -- 对新记录的对应字段执行Trim（插入/更新均处理NEW对象）
    EXECUTE format('NEW.%I = TRIM(NEW.%I)', col.attname, col.attname);
  END LOOP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建事件触发器，自动为新表添加Trim触发器 #

事件触发器会在CREATE TABLE操作完成后，自动为新创建的表添加BEFORE INSERT和BEFORE UPDATE触发器，绑定上面的Trim函数：

CREATE OR REPLACE FUNCTION add_trim_triggers()
RETURNS EVENT_TRIGGER AS $$
DECLARE
  obj RECORD;
BEGIN
  -- 获取刚创建的表对象
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE TABLE'
  LOOP
    -- 避免对系统表添加触发器
    IF obj.schema_name NOT IN ('pg_catalog', 'information_schema') THEN
      -- 创建BEFORE INSERT触发器
      EXECUTE format(
        'CREATE TRIGGER trim_insert BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE FUNCTION trim_text_fields()',
        obj.schema_name, obj.object_name
      );
      -- 创建BEFORE UPDATE触发器
      EXECUTE format(
        'CREATE TRIGGER trim_update BEFORE UPDATE ON %I.%I FOR EACH ROW EXECUTE FUNCTION trim_text_fields()',
        obj.schema_name, obj.object_name
      );
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 创建事件触发器，监听CREATE TABLE及CREATE TABLE AS事件
CREATE EVENT TRIGGER trim_trigger_on_create_table
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE FUNCTION add_trim_triggers();

3. 为已存在的表批量添加Trim触发器 #

如果需要对数据库中已有的表也应用这个规则，可以运行以下SQL批量创建触发器：

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT table_schema, table_name
             FROM information_schema.tables
             WHERE table_type = 'BASE TABLE'
               AND table_schema NOT IN ('pg_catalog', 'information_schema')
  LOOP
    -- 检查是否已存在对应的触发器，避免重复创建
    IF NOT EXISTS (
      SELECT 1 FROM pg_trigger
      WHERE tgrelid = (rec.table_schema || '.' || rec.table_name)::regclass
        AND tgname IN ('trim_insert', 'trim_update')
    ) THEN
      EXECUTE format(
        'CREATE TRIGGER trim_insert BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE FUNCTION trim_text_fields()',
        rec.table_schema, rec.table_name
      );
      EXECUTE format(
        'CREATE TRIGGER trim_update BEFORE UPDATE ON %I.%I FOR EACH ROW EXECUTE FUNCTION trim_text_fields()',
        rec.table_schema, rec.table_name
      );
    END IF;
  END LOOP;
END $$;

注意事项 #

• 性能影响：这个方案会对每个文本字段的插入/更新操作执行Trim，对于高并发、大文本量的场景，可能会有轻微的性能损耗，建议根据业务场景测试后再上线。
• 特殊字段排除：如果某些表的特定文本字段不需要Trim，可以在trim_text_fields函数中添加字段名排除逻辑，比如跳过名为raw_content的字段。
• 权限要求：创建事件触发器需要EVENT TRIGGER权限，创建普通触发器需要对应表的TRIGGER权限。
• 系统表规避：上述代码已经排除了系统表和信息模式下的表，避免对系统数据造成影响。

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