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

Question

我有一个触发器，但是我需要将其与我的所有postgres表相关联。 是否有以下这样的命令？

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

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

- Eduardo Rafael Correa de Souza

---

不，数据库没有全局触发器的支持。你为什么要做这件事呢？ - Craig Ringer

我需要记录数据库操作以与智能手机同步。然后，我将逐个表运行此触发器。感谢您的帮助。 - Eduardo Rafael Correa de Souza

@EduardoRafaelCorreadeSouza 我知道你提出这个问题已经过去了几天，可能你已经手动完成了。但请查看我的答案。如果它让你学到了一些有用的东西，可以帮助你在未来处理类似的任务，那么如果你考虑接受我的答案，那就太好了。 - Gabriel's Messanger

Answer 1

虽然没有数据库级别的触发器创建，但是对于所有这样的批量管理员操作，您可以使用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 模式以及 toast 表从你的 select 中排除。
• 我使用 quote_ident(text) 函数，如果需要 （例如：有空格或大写字母的名称），将字符串放入双引号符号（""）中。
• 当我有了表名列表后，我只需使用一些静态字符串将它们连接起来，以获得我的 SQL 命令。
• 我使用子查询编写该命令，因为我希望您更好地了解这里发生的情况。您可以将 quote_ident(table_schema) || '.' || quote_ident(table_name) 放在 tab_name 的位置上，这样就可以编写单个查询了。

- Gabriel's Messanger

---

我遇到了“FROM子查询必须有别名”的问题，所以在末尾添加了“) AS inner_select;”，然后它就可以工作了。 - Ian Vaughan

@IanVaughan 谢谢，你说得完全正确！我已经更正了我的答案。 - Gabriel's Messanger

Answer 2

一个方便封装的 Gabriel 答案版本。这次我使用触发器来更新一个名为 update_dt datetime 的列，该列被授予作为当前数据库公共模式中任何表的一部分。

--
-- function:  tg_any_update_datetime_fn
-- when:      before insert or update
--
create or replace function tg_any_update_datetime_fn () 
returns trigger 
language plpgsql as $$
begin
  new.update_dt = now();
  return new;
end;
$$;

--
-- function:  ddl_create_before_update_trigger_on_all_tables
-- returns:   Create a before update trigger on all tables.
--
create or replace procedure ddl_create_before_update_trigger_on_all_tables ()
language plpgsql as $$
declare
  _sql varchar;
begin
  for _sql in select concat (
      'create trigger tg_',
      quote_ident(table_name),
      '_before_update before update on ',
      quote_ident(table_name),
      ' for each row execute procedure tg_any_update_datetime_fn ();'
    )
    from
      information_schema.tables
    where  
      table_schema not in ('pg_catalog', 'information_schema') and    
      table_schema not like 'pg_toast%'
  loop
    execute _sql;
  end loop;
end;
$$;

-- create before update trigger on all tables
call ddl_create_before_update_trigger_on_all_tables();

在我的DDL脚本中，我使用了大量的ddl_函数，这些函数仅在DDL时间有意义。要从数据库中删除它们，请使用

--
-- function:  ddl_drop_ddl_functions
-- returns:   Drop all DDL functions.
-- since:     1.1.20
--
create or replace procedure ddl_drop_ddl_functions ()
language plpgsql as $$
declare
  r record;
  _sql varchar;
begin
  for r in
    select oid, prokind, proname
    from   pg_proc
    where  pronamespace = 'public'::regnamespace
    and    proname ilike 'ddl_%'
  loop
    case r.prokind
      when 'a' then _sql = 'aggregate';
      when 'p' then _sql = 'procedure';
      else          _sql = 'function';
    end case;

    _sql = format('drop %s %s', _sql, r.oid::regprocedure);

    execute _sql;
  end loop;
end
$$;

- coterobarros