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:
CREATE TABLE schema_name.sample_audit
(
audit_id bigint NOT NULL DEFAULT nextval('schema_name.sample_audit_id_seq'::regclass),
id bigint NOT NULL,
source table columns ......
action_type character varying(1), 'I', 'D', 'U'
modifiedby character varying(30),
modifiedon timestamp without time zone,
modifiedusing character varying(30),
CONSTRAINT sample_audit_id PRIMARY KEY (audit_id)
)
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.
CREATE OR REPLACE FUNCTION schema_name.generate_audit_trigger(ps_table_name character varying)
RETURNS void AS
$BODY1$
/**
* This function is to generate audit function and trigger for the designated table
* under PostgreSQL.
* ps_table_name - the source table name
* Generated audit function and associated trigger is built on source table
* The mapping audit table name should be: ps_table_name || '_audit'
* The audit table should be created before running this function. The audit table
* consists of following attributes:
* CREATE TABLE schema_name.sample_audit
* (
* audit_id bigint NOT NULL DEFAULT nextval('schema_name.sample_audit_id_seq'::regclass),
* id bigint NOT NULL,
* source table columns ......
* action_type character varying(1), 'I', 'D', 'U'
* modifiedby character varying(30),
* modifiedon timestamp without time zone,
* modifiedusing character varying(30),
* CONSTRAINT sample_audit_id PRIMARY KEY (audit_id)
* )
*/
DECLARE
ls_col character varying;
ls_function_script character varying;
ls_trigger_script character varying;
ls_table_schema character varying;
ls_audit_table_name character varying := '';
ls_column_string character varying := '';
ls_old_column_string character varying := '';
ls_new_column_string character varying := '';
ls_update_column_string character varying := '';
ls_user character varying := '';
cur_columns CURSOR (p_audit_table_name character varying) IS
(select column_name from information_schema.columns
where table_name = p_audit_table_name
and ordinal_position > 1
order by ordinal_position);
BEGIN
ls_audit_table_name := ps_table_name || '_audit';
OPEN cur_columns(ls_audit_table_name);
FETCH cur_columns INTO ls_col;
WHILE FOUND
LOOP
ls_column_string := ls_column_string || ls_col || ', ';
ls_old_column_string := ls_old_column_string || 'old.'|| ls_col || ', ';
ls_new_column_string := ls_new_column_string || 'new.'|| ls_col || ', ';
FETCH cur_columns INTO ls_col;
END LOOP;
CLOSE cur_columns;
select current_user into ls_user;
ls_column_string := substr(ls_column_string, 1, length(ls_column_string) - 2);
ls_old_column_string := substr(ls_old_column_string, 1, length(ls_old_column_string) - 2);
ls_old_column_string := replace(ls_old_column_string, 'old.action_type', '''D''');
ls_old_column_string := replace(ls_old_column_string, 'old.modifiedby', ''''||ls_user||'''');
ls_old_column_string := replace(ls_old_column_string, 'old.modifiedon', 'current_timestamp');
ls_old_column_string := replace(ls_old_column_string, 'old.modifiedusing', '''tf_'||ls_audit_table_name||'''');
ls_new_column_string := substr(ls_new_column_string, 1, length(ls_new_column_string) - 2);
ls_new_column_string := replace(ls_new_column_string, 'new.modifiedby', ''''||ls_user||'''');
ls_new_column_string := replace(ls_new_column_string, 'new.modifiedon', 'current_timestamp');
ls_new_column_string := replace(ls_new_column_string, 'new.modifiedusing', '''tf_'||ls_audit_table_name||'''');
ls_update_column_string := ls_new_column_string;
ls_new_column_string := replace(ls_new_column_string, 'new.action_type', '''I''');
ls_update_column_string := replace(ls_update_column_string, 'new.action_type', '''U''');
select table_schema into ls_table_schema from information_schema.tables where table_name = ls_audit_table_name;
ls_function_script := 'CREATE OR REPLACE FUNCTION ' || ls_table_schema || '.tf_' || ls_audit_table_name || '() RETURNS trigger AS $BODY$
begin
if tg_op = ''DELETE'' then
INSERT INTO '|| ls_table_schema || '.' || ls_audit_table_name || '(' || ls_column_string || ')
SELECT ' || ls_old_column_string || ';
RETURN old;
elseif tg_op = ''INSERT'' then
INSERT INTO '|| ls_table_schema || '.' || ls_audit_table_name || '(' || ls_column_string || ')
SELECT ' || ls_new_column_string || ';
RETURN new;
elseif tg_op = ''UPDATE'' then
INSERT INTO '|| ls_table_schema || '.' || ls_audit_table_name || '(' || ls_column_string || ')
SELECT ' || ls_update_column_string || ';
RETURN new;
end if;
end; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;';
EXECUTE ls_function_script;
ls_trigger_script := 'CREATE TRIGGER trigger_' || ps_table_name || ' AFTER INSERT OR UPDATE OR DELETE ON ' ||
ls_table_schema || '.' || ps_table_name || ' FOR EACH ROW
EXECUTE PROCEDURE ' || ls_table_schema || '.tf_' || ls_audit_table_name || '();';
EXECUTE ls_trigger_script;
END;
$BODY1$
LANGUAGE plpgsql VOLATILE
COST 100;
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.
