Talk:Audit trigger 91plus

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Created page with "in the <nowiki>audit_table</nowiki> 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 s…")
 
(reading / quering the audit table: new section)
Line 3: Line 3:
  
 
--[[User:T.bussmann|T.bussmann]] 18:02, 14 February 2013 (UTC)
 
--[[User:T.bussmann|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.
 +
--[[User:T.bussmann|T.bussmann]] 18:15, 14 February 2013 (UTC)

Revision as of 18:15, 14 February 2013

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)

Personal tools