CODEWITHSUNDEEP
postgresqltriggersplpgsql
cc young
cc young

Reputation: 20203

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?

Upvotes: 22

Views: 9353

Answers (2)

Alexi Theodore
Alexi Theodore

Reputation: 1667

I've found that the command column is basically a dead end, unless you are capable of making your own c-function to process the data. For everyone else, this solution seems to work:

  1. Install the pgddl extension https://github.com/lacanoid/pgddl
  2. Use something like SELECT ddlx_create(objid) FROM pg_event_trigger_ddl_commands() as the ddl text for each object.

I've tested it and it works just about exactly what I would hope for.

Upvotes: 0

Ondřej Bouda
Ondřej Bouda

Reputation: 1061

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

Upvotes: 28

Related Questions