时间戳

首页 > 解决方案 > 选择要更改的表

sql - 选择要更改的表

问题描述

因此，我们有时（过于频繁地）忘记将日志记录触发器添加到我们的某些表中。我现在正在尝试编写一个迁移，它将自动将触发器添加到所有缺少它们的表中。

但是，如何选择要编辑的表？

我可以获得我需要更新的所有表的名称

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`
;

怎么办？

我正在寻找一种方法来做类似的事情：

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 $$;

任何帮助，将不胜感激。

标签： sqlpostgresqlplpgsqldynamic-sqlpostgresql-9.6

解决方案

我认为这会有所帮助，但如果没有，请毫不犹豫地告诉我：

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';

将其放在 do 部分并执行查询结果。

推荐阅读