postgresql - Drop ALL triggers from Postgres DB?

ID : 274520

viewed : 27

Tags : postgresqltriggerspostgresql





Top 5 Answer for postgresql - Drop ALL triggers from Postgres DB?

vote vote

96

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:

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

83

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:

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

72

Take a look in the information_schema:

SELECT * FROM information_schema.triggers; 
vote vote

66

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.

vote vote

56

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 -

DROP LANGUAGE plpgsql CASCADE; 

Top 3 video Explaining postgresql - Drop ALL triggers from Postgres DB?







Related QUESTION?