PostgreSQL: Generating Generic Auditing Trigger

PostgreSQL, as one of the best open source object-relational DBMS, is quite popular today. However, it doesn’t provide built-in auditing function, which is required by most production systems.

Let’s see how we generate generic auditing function for a table in PostgreSQL schema. Then we should be able to generate auditing function for the entire schema easily.

Let’s assume we have an input parameter table name , and we have a pre-built auditing table, which contains all fields from source table, in line 5 below, plus all other auditing fields:

In order to audit a source table, we need an associated trigger and a function, which will be generated by a PostgreSQL function. First, the source table column names need to be sorted out. We may loop through information_schema.columns using a cursor to get all the column names; Second, auditing fields need to be assembled; Third, we generate the scripts to create trigger function (line 95) and trigger (line 100) itself on source table. Whenever any change occurs on source table, the change will be written into the mapping auditing table right away.

This function can be applied to any tables in your schema. Above source code can be downloaded from: generate_audit_trigger.

Lionsgatesoft.com consultant has two decades of rich experience in database design, modeling, migration and development. Should you have any questions, please feel free to contact us.