Routines

Routines

Name Type Language Deterministic Return Type Security Restriction Comments
audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) FUNCTION sql false void INVOKER
unaudit_table(target_table regclass) FUNCTION plpgsql false void INVOKER
audit_table(target_table regclass) FUNCTION sql false void INVOKER

Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored.
unaudit_view(target_view regclass) FUNCTION plpgsql false void INVOKER
if_modified_func() FUNCTION plpgsql false trigger DEFINER

Track changes TO a TABLE at the statement AND/OR row level.

Optional parameters TO TRIGGER IN CREATE TRIGGER call:

param 0: BOOLEAN, whether TO log the query text. default ‘t’.

param 1: text[], COLUMNS TO IGNORE IN updates. default [].

     Updates TO ignored cols are omitted FROM changed_fields.

     Updates WITH only ignored cols changed are NOT inserted
     INTO the audit log.

     Almost ALL the processing work IS still done for updates
     that ignored. If you need to save the load, you need to use
     WHEN clause on the trigger instead.

     No warning or error is issued if ignored_cols contains columns
     that do not exist in the target table. This lets you specify
     a standard set of ignored columns.

There is no parameter to disable logging of values. Add this trigger as a ‘FOR EACH STATEMENT’ rather than ‘FOR EACH ROW’ trigger if you do not want to log row values.

Note that the user name logged is the login role for the session. The audit trigger cannot obtain the active role because it is reset by the SECURITY DEFINER invocation of the audit trigger its self.

[]: ./null []: ./null
replay_event(pevent_id integer) FUNCTION plpgsql false void INVOKER

Replay a logged event.

Arguments: pevent_id: The event_id of the event in tww_sys.logged_actions to replay
audit_view(target_view regclass, audit_query_text boolean, ignored_cols text[], uid_cols text[]) FUNCTION plpgsql false void INVOKER

ADD auditing support TO a VIEW.

Arguments: target_view: TABLE name, schema qualified IF NOT ON search_path audit_query_text: Record the text of the client query that triggered the audit event? ignored_cols: COLUMNS TO exclude FROM UPDATE diffs, IGNORE updates that CHANGE only ignored cols. uid_cols: MANDATORY COLUMNS to use to uniquely identify a row from the view (in order to replay UPDATE and DELETE)

Example: SELECT tww_sys.audit_view(‘view_schema.view_name’, ‘true’::BOOLEAN, ‘field_to_ignore’::text[], ‘obj_id’::text[])

[]: ./null []: ./null
audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, ignored_cols text[]) FUNCTION plpgsql false void INVOKER

ADD auditing support TO a TABLE.

Arguments: target_table: TABLE name, schema qualified IF NOT ON search_path audit_rows: Record each row CHANGE, OR only audit at a statement level audit_query_text: Record the text of the client query that triggered the audit event? ignored_cols: COLUMNS TO exclude FROM UPDATE diffs, IGNORE updates that CHANGE only ignored cols.