Creating a Snapshot of a table which is being Audited v2.0
If a table is being audited it is possible to set up a snapshot of the table by using the command audit.at_audit_create_table_with_pkey.
The command should be run by an admin user.
SELECT audit.at_audit_create_table_with_pkey(source_schema text,source_table text, pkeycolname text, tdate text, destination_schema text DEFAULT NULL::text,destination_table text DEFAULT NULL::text);
The arguments are as follows:
| Argument | Description |
|---|---|
source_schema: | The schema holding the table being audited (e.g. planning) |
source_table: | The table being audited (e.g. application) |
pkeycolname: | The column which is the unique identifier for the audited table. This may be ogc_fid or any other PK column. |
tdate: | The date for the snapshot in format 'YYYY-MM-DD HH24:MI:SS'. |
destination_schema: | The schema to hold the snapshot. If null is supplied then this defaults to the public schema but in the SDW the public schema can not be written to so you need to specify a schema. |
destination_table: | The name of the table to hold the snapshot. If a table with this name already exists it will get dropped and recreated. If NULL is supplied then this defaults to <source_table_> with _<tdate> appended to it. |
select audit.at_audit_create_table_with_pkey('planning', 'applications', 'id', '2016-03-24 16:30:00', 'planning', 'snapshot1');
select audit.at_audit_create_table_with_pkey('planning', 'applications', 'id', to_char(now() - interval '10 min','YYYY-MM-DD HH24:MI:SS'), 'planning');