如何通过获取列名避免同表ALTER TABLE事件触发器递归触发

要解决触发器递归调用的问题，核心是在事件触发器中判断当前ALTER TABLE操作是否针对secret列——只有当操作不涉及该列时，才执行删除并重新添加secret列的逻辑，从而避免递归。

PostgreSQL提供了pg_event_trigger_ddl_commands()函数获取当前DDL命令的元数据，结合current_query()可以精准识别操作涉及的列。以下是具体实现：

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

CREATE OR REPLACE FUNCTION handle_alter_table()
RETURNS event_trigger AS $$
DECLARE
    rec RECORD;
    is_secret_operation BOOLEAN := FALSE;
BEGIN
    -- 遍历当前触发的DDL命令
    FOR rec IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        -- 仅处理目标表的ALTER TABLE操作
        IF rec.command_tag = 'ALTER TABLE' AND rec.objid = 'your_table_name'::regclass THEN
            -- 检查当前DDL是否涉及secret列的操作
            SELECT EXISTS (
                SELECT 1
                WHERE current_query() LIKE '%ALTER TABLE%ALTER COLUMN secret%'
                   OR current_query() LIKE '%ALTER TABLE%DROP COLUMN secret%'
                   OR current_query() LIKE '%ALTER TABLE%ADD COLUMN secret%'
            ) INTO is_secret_operation;
        END IF;
    END LOOP;

    -- 仅当操作不涉及secret列时，执行替换逻辑
    IF NOT is_secret_operation THEN
        ALTER TABLE your_table_name DROP COLUMN IF EXISTS secret;
        ALTER TABLE your_table_name ADD COLUMN secret TEXT DEFAULT 'your_default_value';
    END IF;
END;
$$ LANGUAGE plpgsql;

2. 创建事件触发器 #

CREATE EVENT TRIGGER alter_table_trigger
ON ddl_command_end
WHEN TAG IN ('ALTER TABLE')
EXECUTE FUNCTION handle_alter_table();

关键说明 #

• 替换代码中的your_table_name为实际表名，your_default_value为secret列的默认值
• 通过current_query()解析当前执行的SQL语句，判断是否是针对secret列的操作，避免触发器自身执行的ALTER TABLE触发递归
• 如果需要更精准的判断（比如区分不同类型的列操作），可以结合pg_attribute系统表或pg_get_expr()函数进一步细化逻辑

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