CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sqlpostgresql-9.6
User1291
User1291

Reputation: 8182

Select tables to alter

So we sometimes (too frequently) forget to add logging triggers to some of our tables. I am now trying to write a migration that will automatically add triggers to all the tables that are missing them.

How do I select the tables to edit, though?

I can get the name of all the tables I need to update with 

WITH
    trigger_information AS (
        SELECT schemata.table_name, triggers.trigger_name
            FROM information_schema.triggers triggers
                RIGHT JOIN information_schema."tables" schemata ON schemata.table_name = triggers.event_object_table
            WHERE
                schemata.table_schema = 'public'
    ),
    tables_without_log_trigger AS (
        SELECT DISTINCT table_name
            FROM trigger_information ti1
            WHERE NOT EXISTS (
                SELECT *
                    FROM trigger_information ti2
                    WHERE ti2.table_name = ti1.table_name
                        AND ti2.trigger_name = 'log_action'
            )
    )
-- TODO: add triggers to all tables in `tables_without_log_trigger`
;

Now what?

I'm looking for a way to do something like:

WITH
    trigger_information AS (
        SELECT schemata.table_name, triggers.trigger_name
            FROM information_schema.triggers triggers
                RIGHT JOIN information_schema."tables" schemata ON schemata.table_name = triggers.event_object_table
            WHERE
                schemata.table_schema = 'public'
    ),
    tables_without_log_trigger AS (
        SELECT DISTINCT table_name
            FROM trigger_information ti1
            WHERE NOT EXISTS (
                SELECT *
                    FROM trigger_information ti2
                    WHERE ti2.table_name = ti1.table_name
                        AND ti2.trigger_name = 'log_action'
            )
    )
DO $$
DECLARE
    iterator CURSOR FOR SELECT * FROM tables_without_log_trigger;
    next_table TEXT;
BEGIN
    OPEN iterator;
    LOOP
        FETCH NEXT FROM iterator INTO next_table;
        EXIT WHEN NOT FOUND;

        CREATE TRIGGER log_action
          AFTER INSERT OR UPDATE OR DELETE ON next_table
          FOR EACH ROW EXECUTE PROCEDURE logging.log_action();

    END LOOP;
    CLOSE iterator;
END $$;

Any help would be appreciated.

Upvotes: 0

Views: 45

Answers (1)

Jaisus
Jaisus

Reputation: 1109

I think this is going to help but do not hesitate to tell me if it doesn't :

with cte_all_existing_trigger as
(
    select distinct ta.table_name       
    from information_schema."tables" ta
        inner join information_schema.triggers tr
        on tr.event_object_table = ta.table_name
    where ta.table_schema = 'public'
        and tr.trigger_name like ' log_action%'
)
select string_agg(' CREATE TRIGGER log_action
          AFTER INSERT OR UPDATE OR DELETE ON '||c.table_name||'
          FOR EACH ROW EXECUTE PROCEDURE logging.log_action();', chr(13))
from information_schema."tables" c
where c.table_name not in (select table_name from cte_all_existing_trigger)
    and c.table_schema = 'public';

Put this in a do section and execute the result of the query.

Upvotes: 1

Related Questions