如何从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开始，函数

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;

输出: NOTICE: caught ALTER TABLE event on public.t

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