Re: Event trigger information accessibility on plpgsql

Not really.  When a row has been updated, you can only see the new values.  By comparing the row's xmin value to txid_current() you could tell that it was updated during this transaction, but there is no way to access the old row.  And after a row has been deleted, there is absolutely no way for the current transaction to see it any longer.

I thought that I would try my hand at writing an event trigger that was able to capture ALTER TABLE .. RENAME and ALTER TABLE ... RENAME COLUMN.  Turns out to be far harder than it looks.  After much hacking around, I managed to come up with a solution.  The attached script audit_ddl.sql does the trick.

psql (9.4devel)
Type "help" for help.

postgres=# create table foo(column_one text, column_two integer);
NOTICE:  on_start: taking catalog snapshot...
NOTICE:  on_commit: checking for DDL actions.
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# alter table foo rename column column_one to col1;
NOTICE:  on_start: taking catalog snapshot...
ALTER TABLE
postgres=# alter table foo rename column column_two to col2;
ALTER TABLE
postgres=# commit;
NOTICE:  on_commit: checking for DDL actions.
NOTICE:  on_commit: table foo column column_one renamed to col1.
NOTICE:  on_commit: table foo column column_two renamed to col2.
COMMIT

Regards,
Andrew Tipton