PostgreSQL表重命名时如何单条删除关联的所有触发器与存储过程

批量删除目标表所有触发器 #

PostgreSQL本身没有提供单条语句删光一张表全部触发器的原生语法，直接执行下面的动态SQL即可搞定，不用手动逐个枚举触发器名称：

-- 执行后自动清空employee表上所有用户自定义触发器
DO $$
DECLARE
    batch_drop_sql text;
BEGIN
    SELECT string_agg(format('DROP TRIGGER IF EXISTS %I ON employee;', tgname), ' ')
    INTO batch_drop_sql
    FROM pg_trigger
    WHERE tgrelid = 'employee'::regclass
      AND NOT tgisinternal; -- 自动跳过系统内部生成的约束类触发器，不会误删
    -- 表上有触发器时才执行删除
    IF batch_drop_sql IS NOT NULL THEN
        EXECUTE batch_drop_sql;
    END IF;
END $$;

怕误删的话，可以先把EXECUTE batch_drop_sql;换成RAISE NOTICE '将执行的删除语句：%', batch_drop_sql;，跑一遍看生成的语句对不对，确认没问题了再换回执行语句

批量删除关联目标表的所有存储过程、函数 #

通过系统依赖表检索所有引用了employee表的自定义存储过程和函数，拼好删除语句一次性执行完：

-- 执行后自动删掉所有依赖employee表的存储过程、自定义函数
DO $$
DECLARE
    batch_drop_proc_sql text;
BEGIN
    SELECT string_agg(
        format(
            'DROP %s IF EXISTS %I(%s) CASCADE;',
            CASE p.prokind 
                WHEN 'p' THEN 'PROCEDURE'
                WHEN 'f' THEN 'FUNCTION'
            END,
            p.proname,
            pg_get_function_identity_arguments(p.oid)
        ),
        ' '
    )
    INTO batch_drop_proc_sql
    FROM pg_depend d
    JOIN pg_proc p ON d.refobjid = p.oid
    JOIN pg_class c ON d.classid = c.oid AND c.relname = 'pg_proc'
    WHERE d.refobjid = 'employee'::regclass
      AND p.prokind IN ('f', 'p')
      AND d.deptype = 'n'; -- 只删普通业务对象，跳过系统强制绑定的内置对象
    IF batch_drop_proc_sql IS NOT NULL THEN
        EXECUTE batch_drop_proc_sql;
    END IF;
END $$;

重命名操作注意事项 #

• 跑完上面两段删除逻辑，直接执行重命名语句就行：ALTER TABLE employee RENAME TO "user";
• 重点提醒：user是PostgreSQL的保留关键字，写表名的时候必须加双引号，不然直接报语法错
• 重命名完之后，把之前备份的触发器、存储过程定义里所有提到employee表的地方全替换成新表名"user"，重新执行创建脚本就走完整个流程了。

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