Dynamic auditing of data with PostgreSQL trigger

Question

I'm interested in using the following audit mechanism in an existing PostgreSQL database.

http://wiki.postgresql.org/wiki/Audit_trigger

but, would like (if possible) to make one modification. I would also like to log the primary_key's value where it could be queried later. So, I would like to add a field named something like "record_id" to the "logged_actions" table. The problem is that every table in the existing database has a different primary key fieldname. The good news is that the database has a very consistent naming convention. It's always, _id. So, if a table was named "employee", the primary key is "employee_id".

Is there anyway to do this? basically, I need something like OLD.FieldByName(x) or OLD[x] to get value out of the id field to put into the record_id field in the new audit record.

I do understand that I could just create a separate, custom trigger for each table that I want to keep track of, but it would be nice to have it be generic.

edit: I also understand that the key value does get logged in either the old/new data fields. But, what I would like would be to make querying for the history easier and more efficient. In other words,

select * from audit.logged_actions where table_name = 'xxxx' and record_id = 12345;

another edit: I'm using PostgreSQL 9.1

Thanks!

Craig Ringer · Accepted Answer · 2011-11-29 02:07:51Z

1

You didn't mention your version of PostgreSQL, which is very important when writing answers to questions like this.

If you're running PostgreSQL 9.0 or newer (or able to upgrade) you can use this approach as documented by Pavel:

http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html

In general, what you want is to reference a dynamically named field in a record-typed PL/PgSQL variable like 'NEW' or 'OLD'. This has historically been annoyingly hard, and is still awkward but is at least possible in 9.0.

Your other alternative - which may be simpler - is to write your audit triggers in plperlu, where dynamic field references are trivial.

answered Nov 29, 2011 at 2:07

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David S Over a year ago

Thanks for the reply and for pointing that out. I'm using PostgreSQL 9.1 (and I have updated the original post)

Collectives™ on Stack Overflow

Dynamic auditing of data with PostgreSQL trigger

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related