Viswamitra's blog

Today I got a requirement to create a audit table with below structure

tablename,
columnname,
old value,
new value
…
…

I have created a common function and used that function in all triggers created on the required tables.

Trigger For performing Audit in Postgres:-

— Function: audit_table()

— DROP FUNCTION audit_table();

CREATE OR REPLACE FUNCTION audit_table()
  RETURNS trigger AS
$BODY$
DECLARE
colnames text[];
m text;
old_value text;
new_value text;
    BEGIN
if(TG_OP=’UPDATE’)
then
select array_agg(column_name::text) into colnames from information_schema.columns where table_name=TG_TABLE_NAME;
foreach m IN array colnames
LOOP
EXECUTE format (‘select ($1).%I::text’,m) using OLD into old_value;
EXECUTE format (‘select ($1).%I::text’,m) using NEW into new_value;

if(old_value != new_value)
then
insert into test_audit values(TG_TABLE_NAME,m,old_value,new_value);
return OLD;
end if;
END LOOP;
RETURN NULL;
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION audit_table()
  OWNER TO postgres;

 
 
CREATE TRIGGER test_audit1
  AFTER UPDATE
  ON test_err
  FOR EACH ROW
  EXECUTE PROCEDURE audit_table();