postgresql 从 Postgres DB 中删除所有触发器？

Drop ALL triggers from Postgres DB?

提问by JamesD

Is there any way to drop ALL triggers from ALL tables in Postgres? I know there's a pg_trigger table I could look at, but it doesn't look like it contains enough information for me to decipher which triggers I have added to my tables.

有没有办法从 Postgres 的所有表中删除所有触发器？我知道有一个 pg_trigger 表我可以查看，但它似乎没有包含足够的信息让我破译我添加到我的表中的触发器。

It also looks like Foreign Key constraints show up in the pg_trigger table, which I DO NOT want to drop. I just want to drop the user created trigger from my tables and keep the FKs.

看起来外键约束也出现在 pg_trigger 表中，我不想删除它。我只想从我的表中删除用户创建的触发器并保留 FK。

Any suggestions?

有什么建议？

回答by smith3v

Thanks, James.

谢谢，詹姆斯。

The function from Drop ALL triggers from Postgres DB?strips only the occurrence from the first table and leaves the triggers with the same name in other tables. Here is the fixed function:

Drop ALL的函数从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();

回答by Pushpak Dagade

Simply cascade drop the language in which you created the triggers.
For example, I create triggers in plpgsql, so the following query deletes all triggers instantaneously -

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

DROP LANGUAGE plpgsql CASCADE;

回答by Drux

I prefer this (based on that) over the accepted answer by @kuznetso3v because it gives me a chance to inspect the DROP STATEMENTs before executing them with copy-paste:

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

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

回答by JamesD

UPDATE:See the real solution for the full function you want.

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

Alright, I came up with a function that does this for me:

好吧，我想出了一个为我做这个的函数：

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 'done';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

select strip_all_triggers();

That will drop any trigger in your public schema.

这将删除您的公共架构中的任何触发器。

回答by Tometzky

Easiest will be to pg_dump -sobject definitions and filter it for lines starting with CREATE TRIGGER.

最简单的方法是pg_dump -s对象定义并过滤以CREATE TRIGGER.

Something like

就像是

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

(in bash) should work (review it and then run in database).

（在 bash 中）应该可以工作（查看它然后在数据库中运行）。

But perhaps you should consider alter table table_name disable trigger trigger_nameinstead.

但也许你应该考虑一下alter table table_name disable trigger trigger_name。

回答by Frank Heikens

Take a look in the information_schema:

看一下information_schema：

SELECT * FROM information_schema.triggers;

回答by pcent

You could start from this query, to find outr trigger names:

您可以从这个查询开始，找出 outr 触发器名称：

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

回答by Jitendra Loyal

The top answer is still flawed because there is no need for two loops.

最佳答案仍然有缺陷，因为不需要两个循环。

It can be done by:

可以通过以下方式完成：

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;

回答by gargii

I made two improvements of the solution from the older answers:

我从旧答案中对解决方案进行了两项改进：

• added filtering by trigger name, table and schema
• properly handling "truncate" triggers (ignored by the original solution)

• 添加了按触发器名称、表和架构过滤
• 正确处理“截断”触发器（被原始解决方案忽略）

---

---

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();