如何从 Postgres 事件触发器获取 SQL 文本 [英] How to get SQL text from Postgres event trigger

问题描述

在标记 ALTER TABLE 上的 pgsql 事件触发器中，我想知道哪个表正在被更改.

In a pgsql event trigger on tag ALTER TABLE, I wish to know which table is being altered.

pg 变量不包括这一点，GET STACKED DIAGNOSTICS 公开的变量也不包括.

The pg variables do not cover this, nor do the variables exposed by GET STACKED DIAGNOSTICS.

在变量可用的情况下，触发器函数本身是否有任何方法可以查看负责启动该函数的 SQL 命令的文本.

With variables available, is there any way within the trigger function itself to see the text of the SQL command responsible for initiating the function.

例如，如果

ALTER TABLE base1 ADD COLUMN col1 int;

分别负责调用事件触发器，有没有什么办法可以在事件触发器内查看然后ALTER TABLE base1 ADD COLUMN col1 int文本本身?

were responsible for calling the event trigger, is there any way within the event trigger to see then ALTER TABLE base1 ADD COLUMN col1 int text itself?

推荐答案

从 PostgreSQL 9.5 开始，函数 pg_event_trigger_ddl_commands() 可用于 ddl_command_end 事件触发器.使用 TAG 过滤器，它可用于处理任何 ALTERed 表.object_identity(或objid)可以用来解决原来知道哪个表被修改的问题.至于获取完整命令，也是可以的，不过是内部类型pg_ddl_command.

Starting from PostgreSQL 9.5, function pg_event_trigger_ddl_commands() is available for ddl_command_end event triggers. Using the TAG filter, it may be used for processing any ALTERed table. object_identity (or objid) may be used to solve the original problem of knowing which table has been ALTERed. As for getting the complete command, it is available, too, but it is of an internal type pg_ddl_command.

CREATE TABLE t (n INT);

CREATE FUNCTION notice_event() RETURNS event_trigger AS $$
DECLARE r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER tr_notice_alter_table
  ON ddl_command_end WHEN TAG IN ('ALTER TABLE')
  EXECUTE PROCEDURE notice_event();

ALTER TABLE t ADD c CHAR;

输出:注意:在 public.t 上捕获了 ALTER TABLE 事件

这篇关于如何从 Postgres 事件触发器获取 SQL 文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！