Drop ALL triggers from Postgres DB?

Question

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.

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.

Any suggestions?

Answer 1

Take a look in the information_schema:

SELECT * FROM information_schema.triggers;

Answer 2

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

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

Answer 3

Easiest will be to pg_dump -s object definitions and filter it for lines starting with 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).

But perhaps you should consider alter table table_name disable trigger trigger_name instead.

Answer 4

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.