To restore a single value from the JSONB logs for a given column and row the following indentifiers are necessary:
- A logged transaction ID you want to return to
audit_idto address the row (which implicitly defines the table)
- The (historic) column name
- The (historic) data type of the column
SELECT pgmemento.restore_value(10, 555, 'column_B', NULL::text);
Note: The last argument in
pgmemento.restore_value is designed to be a template that is used in the function body and then returned to the user. That's why it's NULL in the beginning. It is of type
anyelement. Thus, the returned data type is the same as the one you have chosen as the input.
If the column name and data type have changed over time you might need to check the
audit_column_log table, to find out what has been the column name and data type before the given transaction id, e.g.
c.audit_table_id = t.id
AND c.txid_range @> 10::numeric
AND t.table_name = 'table_A'
Restore only if changed
pgmemento.restore_value will always return a value as long as a log for this column is found the
row_log table which has been inserted before or during the given transaction. To get a historic value that was inserted exactly at the given transaction call the
SELECT pgmemento.restore_change(10, 555, 'column_B', NULL::text);
This query will be faster as the whole history before the transaction does not have to be scanned. It is used for example to revert
ALTER COLUMN events.