Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

postgresql - How to get SQL text from Postgres event trigger

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

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

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.

for example, if

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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;

outputs: NOTICE: caught ALTER TABLE event on public.t


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...