To restore a complete tuple use the function
pgmemento.restore_record. It requires the following inputs:
start_from_tid- transaction ID from where you want to start your search
end_at_tid- upper transaction ID filter that defines the version of the tuple
table_name- name of the table the record belongs to
schema_name- name of the schema the table belongs to
audit_idthat defines which row you are intrested in
jsonb_output- flag to choose for relational of JSONB layout
Note: The temporal filter is defined by the second transaction ID parameter. The first argument is only useful to test if the given row has changed within the transaction ID window. If no event occurred the function returns nothing. So, use 1 to be sure, a record is returned (requires a baseline).
Column definition list
pgmemento.restore_record returns any record. So, it is necessary to provide a column definition list with the column names and data types which have to match the logs. Again, the
audit_column_log could be queried but for convenience you can use the function
pgmemento.restore_record_definition that returns the correct column definition list for you. If your
audit_id column name is different than the default
pgmemento_audit_id, specify it in the last argument.
SELECT pgmemento.restore_record_definition(10, 'table_A', 'public', 'audit_trail_id');
AS (id integer, column_B text, column_C character, audit_trail_id bigint) ```
The returned text can be attached to the following query:
pgmemento.restore_record(1, 10, 'table_A', 'public', 555)
AS (id integer, column_B text, column_C character, audit_trail_id bigint);
Things become simpler if you want to return historic tuples as JSONB, as the column definition list is reduced to one JSONB column. Note, that the last (optional) argument of
pgmemento.restore_record has to be TRUE.
pgmemento.restore_record(1, 10, 'table_A', 'public', 555, TRUE)
AS (log JSONB);
The restore query
Another possibility is to call the
pgmemento.restore_query function which returns the query string that is also used by
SELECT pgmemento.restore_query(1, 10, 'table_A', 'public', 555);
The query text can be quite long depending on the number of columns. So, better export the result into a SQL file. Now, it can be used to retrieve a given record in a relational layout without a column definition list.