Drop ALL triggers from Postgres DB?
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();
Take a look in the information_schema:
SELECT * FROM information_schema.triggers;
I prefer this (based on that) over the accepted answer by @kuznetso3v because it gives me a chance to inspect the DROP STATEMENT
s before executing them with copy-paste:
SELECT 'DROP TRIGGER ' || trigger_name || ' ON ' || event_object_table || ';'
FROM information_schema.triggers
WHERE trigger_schema = 'public';