如何为postgresql中的所有表创建触发器？ [英] How to create trigger for all table in postgresql?

问题描述

我有一个触发器，但我需要关联我的postgres的所有表。
有这样的命令吗？

I have a trigger, but I need to associate with all tables of the my postgres. Is there a command like this below?

CREATE TRIGGER delete_data_alldb
BEFORE DELETE
ON ALL DATABASE
FOR EACH ROW
EXECUTE PROCEDURE delete_data();

推荐答案

所有这些批量管理操作，您可以使用PostgreSQL系统表来为您生成查询，而不是手动编写。
在这种情况下，您可以运行：

Well there is no database-wide trigger creation but for all such bulk-admin-operations you could use PostgreSQL system tables to generate queries for you instead of writing them by hand. In this case you could run:

SELECT
    'CREATE TRIGGER '
    || tab_name
    || ' BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();' AS trigger_creation_query
FROM (
    SELECT
        quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name
    FROM
        information_schema.tables
    WHERE
        table_schema NOT IN ('pg_catalog', 'information_schema')
        AND table_schema NOT LIKE 'pg_toast%'
) tablist;

这将得到一组SQL命令的字符串，如：

This will get you set of strings which are SQL commands like:

CREATE TRIGGER schema1.table1 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema1.table2 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema1.table3 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema2.table1 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema2."TABLE2" BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
...
etc

您只需要立即运行通过 psql 或pgAdmin）。

You just need to run them at once (either by psql or pgAdmin).

现在有一些解释：

• 我使用 information_schema.tables 系统表在数据库中选择表的名称。因为有字面上所有表的数据，请记住从<$ c>中排除 pg_catalog 和 information_schema $ c> select 。


• 我使用 quote_ident（text）


• 当我有表的列表时，如果需要，可以使用符号（


• 我使用子查询编写该命令，因为我想让您更好地了解这里发生了什么。您可以通过放置 quote_ident（table_schema）||来编写单个查询'。'|| tab_name



• I select names of tables in my database using information_schema.tables system table. Because there are data of literally all tables, remember to exclude pg_catalog and information_schema schemas and toast tables from your select.
• I use quote_ident(text) function which will put string inside double quote signs ("") if necessary (ie. names with spaces or capital letters require that).
• When I have list of tables names I just concatenate them with some static strings to get my SQL commands.
• I write that command using sub-query because I want you to get better idea of what's going on here. You may write a single query by putting quote_ident(table_schema) || '.' || quote_ident(table_name) in place of tab_name.

这篇关于如何为postgresql中的所有表创建触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！