CODEWITHSUNDEEP
postgresqltriggers
Jesper
Jesper

Reputation: 2094

Drop triggers for all tables

I have multiple triggers, all being the same trigger, but on different tables e.g.

  tgname         |          trigger_table           
------------------------+----------------------------------
 updated_agg_trigger    | forecasts.forecast_1548180000
 updated_agg_trigger    | forecasts.forecast_1548187200
 updated_agg_trigger    | forecasts.forecast_1548162000
 updated_agg_trigger    | forecasts.forecast_1548190800
 updated_agg_trigger    | forecasts.forecast_1548183600
 updated_agg_trigger    | forecasts.forecast_1548172800
 updated_agg_trigger    | forecasts.forecast_1548111600
 updated_agg_trigger    | forecasts.forecast_1548136800
 updated_agg_trigger    | forecasts.forecast_1548129600

How can I delete all updated_agg_trigger-triggers on all tables with a single command?

P.S. I don't want to delete ALL triggers, just the updated_agg_trigger-triggers.

Upvotes: 1

Views: 370

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

Run dynamic statement looping through information_schema.triggers.

DO $$
DECLARE
l_trig_name TEXT := 'updated_agg_trigger';
l_schema    TEXT := 'forecasts';
rec record;
BEGIN

for rec IN
(
  SELECT event_object_table as table_name
      ,trigger_schema as schema_name
      ,trigger_name
   FROM  information_schema.triggers
      WHERE  trigger_name   = l_trig_name
       AND trigger_schema   = l_schema
 ) 
   LOOP
        EXECUTE format ('DROP TRIGGER %I 
                ON %I.%I', rec.trigger_name,rec.schema_name,rec.table_name);
   END LOOP;

END $$;

Upvotes: 2

Related Questions