[SOLVED] Get Table Name of PostgreSQL Event Trigger

Get Table Name of PostgreSQL Event Trigger

I have multiple tables in a PostgreSQL 9.6 database which changes I want to monitor and handle in an extern application.

Handling only data changes wasn't hard, but now I want to monitor structure changes of my database to be stored. This is what I have:

CREATE OR REPLACE FUNCTION log_structureChanged()
    RETURNS event_trigger AS $$
BEGIN
    UPDATE dbchanged SET changed=2 WHERE table_name = TG_ARGV[0];
    RETURN NEW;
END; 
$$ LANGUAGE PLPGSQL;


CREATE EVENT TRIGGER testData_struc
    ON ddl_command_end 
    WHEN TAG IN ( 'CREATE TABLE', 'ALTER TABLE', 'DROP TABLE' )
    EXECUTE PROCEDURE log_structureChanged();

When the EXECUTE PROCEDURE gets called, I want to parse the table that the changes have been made to. The official PostgreSQL documentation didn't really help me - I also may have not fully understood some parts.

So how do I parse the table on that the EVENT TRIGGER fired on? Is it stored inside a variable?

Solution

As described in the documentation, you can call the function pg_event_trigger_ddl_commands(), which will return one row per affected object.

You can either use the result column object_identity which contains a textual description of the affected object, or classid and objid, which contain the object ID of the catalog table that contains the object and the object ID of the affected object.