Well in versions 9.3 and 9.4 event triggers are something like “very good start”.
You can use them quite well but it is not exactly 100% comfortable.
How to use them:
- Create function which returns “event_trigger”. Function will end with just “RETURN;”.
- In this function you can basically use variables / functions like:
- tg_event – to get event category which triggered trigger – “ddl_command_end”, ….
- tg_tag – to get category of command which triggered trigger – “ALTER TABLE”, “CREATE TABLE”, “CREATE FUNCTION”, …
- pg_event_trigger_dropped_objects() function – to get detailed information about dropped objects – returns set of records
- current_query() function – to get full text of query which triggered trigger – this is right now (9.4.1) the only possibility to check which object was altered or created – so you will have to analyze query text
- of course all other system functions are also available
- Create event trigger using command – “create event trigger ….. on ….. execute procedure …..;”
Event trigger is global for the whole database and is triggered with every command in given category regardless of schema or object name.
Therefore if you want to check / log only some actions or only some objects you have to embed logic for it into your event trigger function.