Talk:Audit trigger 91plus

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(problem with schema: new section)
 
Line 16: Line 16:
 
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.
 
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)
 
--[[User:T.bussmann|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.

Latest revision as of 09:19, 21 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)

[edit] 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)

[edit] 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.

Personal tools