Talk:Audit trigger 91plus
From PostgreSQL wiki
in the audit_table function you are getting a parameter target_table of type regclass which you cast to text and quote_ident afterwards to use in a dynamic SQL statement. To my understanding, the quote_ident can be safely removed and should as the current form makes it impossible to use the trigger on anything that needs a fully qualified name (not in search_path) furthermore, I don't understand why you change the search_path in the middle of the script: SET search_path = pg_catalog, public; Can you explain?
--T.bussmann 18:02, 14 February 2013 (UTC)
reading / quering the audit table
as there were no samples given, I've experimented a bit with the possibilities to make any use of the audit table:
select (populate_record(null::my_table_name, a.row_data)).* from audit.logged_actions a
where a.table_name = 'my_table_name' and (a.row_data -> 'primary_key') = 'pk_value';
not sure this is the best way to do, thus. Comments welcome.
The audit system doesn't require any changes on rename or structural changes to the audited table, which is nice. To read the data back, you need to take care of this, by suppling a matching record type, thus. --T.bussmann 18:15, 14 February 2013 (UTC)
problem with schema
Effectively, the quote_ident calls are useless and prevent the use of the function with schema defined regclass (i.e. schema.table).
So, to make the trigger available on other schemas than public, remove the quote_ident.