1

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.

Jesper
  • 2,044
  • 3
  • 21
  • 50
  • https://stackoverflow.com/questions/33699480/postgresql-delete-multiple-rows-from-multiple-tables – ecp May 29 '19 at 09:33

1 Answers1

2

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 $$;
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Thought it worked, but it didn't. I'm getting ERROR: relation "forecast_1548180000" does not exist CONTEXT: SQL statement "DROP TRIGGER updated_agg_trigger ON forecast_1548180000" – Jesper May 29 '19 at 10:04
  • Okay, so I just had to add the schema in the `DROP TRIGGER...` format i.e. `EXECUTE format ('DROP TRIGGER %I ON forecasts.%I', l_trig_name,rec.table_name);` – Jesper May 29 '19 at 10:06
  • 1
    @Jesper : Yes, it was my mistake as well. I've changed my code, which is more generalised – Kaushik Nayak May 29 '19 at 10:12