database - 如何在PostgreSQL中为所有表创建触发器？

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

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

最佳答案

嗯，没有在数据库范围内创建触发器，但是对于所有此类批量管理操作，您可以使用PostgreSQL系统表为您生成查询，而无需手动编写查询。
在这种情况下，您可以运行:

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命令，例如:

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)。

现在一些解释:

我使用information_schema.tables系统表选择数据库中表的名称。因为实际上有所有表的数据，所以请记住从pg_catalog中排除information_schema和select模式以及烤面包表。

我使用quote_ident(text)函数，该函数会在必要时将字符串放在双引号("")内(即，带有空格或大写字母的名称需要这样做)。

当我有表名列表时，我只是将它们与一些静态字符串连接起来以获取我的SQL命令。

我使用子查询编写该命令，因为我希望您更好地了解这里的情况。您可以通过将quote_ident(table_schema) || '.' || quote_ident(table_name)代替tab_name来编写单个查询。