从Postgres数据库中删除所有触发器？

Question

有没有办法从Postgres的所有表中删除所有触发器？ 我知道有一个pg_trigger表，但看起来它不包含足够的信息让我解密我添加到表中的哪些触发器。此外，外键约束似乎也出现在pg_trigger表中，而我不想删除。我只想从我的表中删除用户创建的触发器并保留FK。

有什么建议吗？

- JamesD

Answer 1

谢谢，James。

来自Drop ALL triggers from Postgres DB?的函数仅删除第一个表中的触发器，并保留其他表中具有相同名称的触发器。这是修复后的函数：

CREATE OR REPLACE FUNCTION strip_all_triggers() RETURNS text AS $$ DECLARE
    triggNameRecord RECORD;
    triggTableRecord RECORD;
BEGIN
    FOR triggNameRecord IN select distinct(trigger_name) from information_schema.triggers where trigger_schema = 'public' LOOP
        FOR triggTableRecord IN SELECT distinct(event_object_table) from information_schema.triggers where trigger_name = triggNameRecord.trigger_name LOOP
            RAISE NOTICE 'Dropping trigger: % on table: %', triggNameRecord.trigger_name, triggTableRecord.event_object_table;
            EXECUTE 'DROP TRIGGER ' || triggNameRecord.trigger_name || ' ON ' || triggTableRecord.event_object_table || ';';
        END LOOP;
    END LOOP;

    RETURN 'done';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

select strip_all_triggers();

- smith3v

---

这是这个答案第二次帮助我，如果可以的话，我会再点+1。 - Eliseo Ocampos

information_schema缺少TRUNCATE触发器，因此请使用类似以下的语句：SELECT tgname AS trigger_name, relname AS event_object_table FROM pg_trigger INNER JOIN pg_class ON pg_class.oid = tgrelid。 - Lloeki

我发布了自己的答案，正确处理TRUNCATE触发器。https://dev59.com/PnA75IYBdhLWcg3w0cjx#57577258 - gargii

请用 quote_ident(triggNameRecord.trigger_name) 和 quote_ident(triggTableRecord.event_object_table) 替换任何同时也是Postgres关键字的字符串，以正确引用它们。 - Mark McElroy

Answer 2

我更喜欢这个答案（基于这里）而不是被@kuznetso3v接受的答案，因为它让我有机会在复制粘贴之前检查DROP STATEMENT：

SELECT 'DROP TRIGGER ' || trigger_name || ' ON ' || event_object_table || ';'
FROM information_schema.triggers
WHERE trigger_schema = 'public';

- Drux

---

我们能否只是删除选择语句以实际删除它？ - not-a-bug

Answer 3

请在information_schema中查看：

SELECT * FROM information_schema.triggers;

- Frank Heikens

---

使用SELECT trigger_name FROM information_schema.triggers; 更好。 - Evgeny

Answer 4

只需级联删除您创建触发器的语言。
例如，我使用plpgsql创建触发器，因此以下查询会立即删除所有触发器 -

DROP LANGUAGE plpgsql CASCADE;

- Pushpak Dagade

---

你看起来像是一个喜欢冒险的人。 - Kind Contributor

错误：无法删除语言plpgsql，因为扩展plpgsql需要它 提示：您可以删除扩展plpgsql。 - Rambatino

Answer 5

更新：查看您需要的完整功能的真正解决方案。

好的，我想出了一个可以为我完成此操作的函数：

CREATE OR REPLACE FUNCTION strip_all_triggers() RETURNS text AS $$ DECLARE
        triggNameRecord RECORD;
    triggTableRecord RECORD;
BEGIN
    FOR triggNameRecord IN select distinct(trigger_name) from information_schema.triggers where trigger_schema = 'public' LOOP
        SELECT distinct(event_object_table) INTO triggTableRecord from information_schema.triggers where trigger_name = triggNameRecord.trigger_name;
        RAISE NOTICE 'Dropping trigger: % on table: %', triggNameRecord.trigger_name, triggTableRecord.event_object_table;
        EXECUTE 'DROP TRIGGER ' || triggNameRecord.trigger_name || ' ON ' || triggTableRecord.event_object_table || ';';
    END LOOP;
RETURN '完成';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
select strip_all_triggers();

这将删除公共模式中的任何触发器。

- JamesD

Answer 6

最简单的方法是使用pg_dump -s导出对象定义，并过滤以CREATE TRIGGER开头的行。

类似于：

./pg_dump -s db_name | grep '^CREATE TRIGGER' | \
  while read _ _ triggername _; do \
    echo drop trigger "$triggername;"; \
  done

（在bash中）应该可以工作（检查一下，然后在数据库中运行）。

但也许你应该考虑使用alter table table_name disable trigger trigger_name。

- Tometzky

Answer 7

我对旧答案的解决方案进行了两个改进：

• 添加了按触发器名称、表和模式进行过滤的功能
• 正确处理“truncate”触发器（原始解决方案忽略了这一点）

---

CREATE OR REPLACE FUNCTION strip_all_triggers() RETURNS text AS $$ DECLARE
    triggRecord RECORD;
BEGIN
    create temp table all_triggers on commit drop as (
        SELECT tgname AS trigger_name, n.nspname as trigger_schema,  relname as trigger_table
        FROM pg_trigger
                 JOIN pg_class ON pg_class.oid = tgrelid
                 JOIN pg_namespace n ON n.oid = pg_class.relnamespace);

    FOR triggRecord IN select distinct on (trigger_schema, trigger_table, trigger_name) trigger_schema, trigger_table, trigger_name from all_triggers
                       where trigger_schema like 'public' and trigger_name like '%' -- MY FILTER
        LOOP
            RAISE NOTICE 'Dropping trigger: % on table: %.%', triggRecord.trigger_name, triggRecord.trigger_schema, triggRecord.trigger_table;
            EXECUTE 'DROP TRIGGER ' || triggRecord.trigger_name || ' ON ' || triggRecord.trigger_schema || '.' || triggRecord.trigger_table || ';';
        END LOOP;

    RETURN 'done';
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

select strip_all_triggers();

- gargii

Answer 8

在 Drux 的回复之后（谢谢！），我添加了“distinct”关键字以消除破坏批量查询运行的重复语句。

SELECT distinct 'DROP TRIGGER ' || trigger_name || ' ON ' || event_object_table || ';'
FROM information_schema.triggers
WHERE trigger_schema = 'public';

- Ruben1

Answer 9

你可以从这个查询开始，以找到触发器的名称：

select * from pg_trigger t,pg_proc where
 pg_proc.oid=t.tgfoid

- pcent

Answer 10

最佳答案仍然有缺陷，因为没有必要使用两个循环。

可以通过以下方式完成：

CREATE PROCEDURE _DropTableTriggers()
AS
$$
DECLARE
    _rec    RECORD;
BEGIN
    FOR _rec IN
        SELECT  DISTINCT    event_object_table, trigger_name
        FROM    INFORMATION_SCHEMA.triggers
    LOOP
        EXECUTE 'DROP TRIGGER ' || _rec.trigger_name || ' ON ' || _rec.event_object_table || ';';
    END LOOP;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

- Jitendra Loyal