PostgreSQL：约束table_data表的表列合法性并实现自动同步

首先修正原DDL的PostgreSQL兼容语法（PostgreSQL不支持auto_increment，使用标准的GENERATED ALWAYS AS IDENTITY）：

CREATE TABLE table_data 
(
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    table_name VARCHAR(255) NOT NULL,
    column_name VARCHAR(255) NOT NULL,
    column_type VARCHAR(255),
    CONSTRAINT unique_table_column UNIQUE (table_name, column_name)
);

CREATE TABLE table1 
(
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL
);

需求1：插入/更新时校验表与列存在性 #

PostgreSQL无法直接用CHECK约束查询系统表（CHECK不允许调用不稳定函数），因此使用触发器函数+行级触发器实现校验：

1. 创建校验触发器函数 #

CREATE OR REPLACE FUNCTION validate_table_column_exists()
RETURNS TRIGGER AS $$
BEGIN
    -- 校验目标表是否存在于当前模式
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_schema = current_schema() 
          AND table_name = NEW.table_name
    ) THEN
        RAISE EXCEPTION '表 "%" 不存在于当前模式', NEW.table_name;
    END IF;

    -- 校验目标列是否存在于指定表
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = current_schema() 
          AND table_name = NEW.table_name 
          AND column_name = NEW.column_name
    ) THEN
        RAISE EXCEPTION '表 "%" 中不存在列 "%"', NEW.table_name, NEW.column_name;
    END IF;

    -- 自动填充列类型（可选，按需开启）
    SELECT data_type INTO NEW.column_type
    FROM information_schema.columns 
    WHERE table_schema = current_schema() 
      AND table_name = NEW.table_name 
      AND column_name = NEW.column_name;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到table_data #

CREATE TRIGGER trigger_validate_table_column
BEFORE INSERT OR UPDATE ON table_data
FOR EACH ROW EXECUTE FUNCTION validate_table_column_exists();

测试示例 #

• 合法插入（执行无错误）：

  INSERT INTO table_data (table_name, column_name) VALUES ('table1', 'name');
  

• 非法插入（触发错误）：

  -- 表不存在的情况
  INSERT INTO table_data (table_name, column_name) VALUES ('invalid_table', 'col');
  -- 列不存在的情况
  INSERT INTO table_data (table_name, column_name) VALUES ('table1', 'invalid_col');
  

需求2：自动同步表元数据（删除列时删除对应行，自动插入/更新列信息） #

使用事件触发器监控DDL操作，自动同步table_data的内容：

1. 创建同步事件触发器函数 #

CREATE OR REPLACE FUNCTION sync_table_data()
RETURNS EVENT_TRIGGER AS $$
DECLARE
    rec RECORD;
    target_table TEXT;
    target_column TEXT;
    col_type TEXT;
BEGIN
    -- 处理CREATE TABLE：插入新表的所有列
    IF tg_tag = 'CREATE TABLE' THEN
        SELECT objid::regclass INTO target_table FROM pg_event_trigger_ddl_commands();
        FOR rec IN 
            SELECT col.column_name, col.data_type
            FROM information_schema.columns col
            WHERE col.table_schema = current_schema() 
              AND col.table_name = target_table
        LOOP
            INSERT INTO table_data (table_name, column_name, column_type)
            VALUES (target_table, rec.column_name, rec.data_type)
            ON CONFLICT (table_name, column_name) DO UPDATE 
            SET column_type = EXCLUDED.column_type;
        END LOOP;
    END IF;

    -- 处理ALTER TABLE ADD COLUMN：插入新增列
    IF tg_tag = 'ALTER TABLE ADD COLUMN' THEN
        SELECT objid::regclass INTO target_table FROM pg_event_trigger_ddl_commands();
        SELECT arg INTO target_column FROM pg_event_trigger_ddl_commands();
        SELECT data_type INTO col_type
        FROM information_schema.columns 
        WHERE table_schema = current_schema() 
          AND table_name = target_table 
          AND column_name = target_column;
        
        INSERT INTO table_data (table_name, column_name, column_type)
        VALUES (target_table, target_column, col_type)
        ON CONFLICT (table_name, column_name) DO UPDATE 
        SET column_type = EXCLUDED.column_type;
    END IF;

    -- 处理ALTER TABLE DROP COLUMN：删除对应行
    IF tg_tag = 'ALTER TABLE DROP COLUMN' THEN
        SELECT objid::regclass INTO target_table FROM pg_event_trigger_ddl_commands();
        SELECT arg INTO target_column FROM pg_event_trigger_ddl_commands();
        
        DELETE FROM table_data
        WHERE table_name = target_table 
          AND column_name = target_column;
    END IF;

    -- 处理ALTER TABLE ALTER COLUMN：更新列类型
    IF tg_tag = 'ALTER TABLE ALTER COLUMN' THEN
        SELECT objid::regclass INTO target_table FROM pg_event_trigger_ddl_commands();
        SELECT arg INTO target_column FROM pg_event_trigger_ddl_commands();
        SELECT data_type INTO col_type
        FROM information_schema.columns 
        WHERE table_schema = current_schema() 
          AND table_name = target_table 
          AND column_name = target_column;
        
        UPDATE table_data
        SET column_type = col_type
        WHERE table_name = target_table 
          AND column_name = target_column;
    END IF;
END;
$$ LANGUAGE plpgsql;

2. 创建事件触发器 #

CREATE EVENT TRIGGER event_sync_table_data
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE ADD COLUMN', 'ALTER TABLE DROP COLUMN', 'ALTER TABLE ALTER COLUMN')
EXECUTE FUNCTION sync_table_data();

测试示例 #

• 创建新表：

  CREATE TABLE table2 (id INT, email VARCHAR(255));
  -- 自动将table2的id、email列插入table_data
  

• 删除列：

  ALTER TABLE table1 DROP COLUMN username;
  -- 自动删除table_data中table1.username的对应行
  

• 修改列类型：

  ALTER TABLE table2 ALTER COLUMN email TYPE TEXT;
  -- 自动更新table_data中table2.email的column_type为text
  

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