You are not only interested in the historic version before the second transaction ID parameter and want to inspect the evolution of one row? Then, use the `pgmemento.restore_records` function. This will return all versions within the transaction ID frame. Narrowing the filter window can be really helpful if your audit trail is already quite long. The returned rows also include the corresponding event_id and transaction_id to facilitate the analysis of your audit trail. Therefore, the column definition list needs to be extended by these two columns. Again, the `pgmemento.restore_record_definition` function is of help, but when restoring multiple versions is has to be called with different parameters. Like `pgmemento.restore_records` it needs two transaction ID values. Instead of passing the table and schema name you have to pass the table's `log_id` from the `audit_table_log` table as the table could have been ranamed. To get the `log_id` of an already deleted table or renamed table either query the `pgmemento.audit_table_log` table or use the helper functions by pgMemento. ```sql SELECT pgmemento.restore_record_definition(1, 10, 1); restore_record_definition -------------------------------------------------------------------------------------------------------------------- AS (id integer, column_B text, column_C character, audit_trail_id bigint, event_id integer, transaction_id integer) SELECT * FROM pgmemento.restore_records(1, 10, 'table_A', 'public', 555) AS (id integer, column_B text, column_C character, audit_trail_id bigint, event_id integer, transaction_id integer); ``` The queried versions can, of course, be restored to JSONB, too. ## Consider schema changes If the table layout of the rows you want to restore has changed, pgMemento will try to restore columns from all versions. To avoid column ambiguity, a count suffix will be appended to the names. The `pgmemento.restore_record_definition` function also follows this behaviour. _**Note:**_ In the final result there is no way to see, when a column version did not exist. You can only guess when a value hops from one column to another. The JSONB output would be a perfect fit to tackle this problem, but so far the conversion happens after the restore process. Thus, multiple column versions also appear in the JSONB result. However, there is a workaround to get JSONB objects with distinct keys (see next paragraph). RENAME COLUMN events represent an edge case which does not produce a row version because nothing is logged in the `pgmemento.row_log` table. However, the renamed column does appear in the result set but without any values. This might change in the future. For now, as soon as an event is logged in the `row_log` table the current value of the renamed column will appear in the result and the value of the previous version will be NULL. ## Multiple version with `restore.record` Instead of calling `pgmemento.restore_records` to get all versions at once, it is also possible to iterate over all events with the `pgmemento.restore.record` function with JSONB output. This can be done with a LATERAL construct. To see the evolution of a tuple for all events use a fixed lower boundary for the transaction ID window. To see logs only when a change ocurred use a lead window functions to get pairs of subsequent events (see outcommented field in query): ```sql SELECT i.filter_tid, i.table_operation, j.log FROM ( SELECT first_value(e.transaction_id) OVER () AS first_tid, e.transaction_id AS filter_tid, --e.transaction_id AS first_tid, --COALESCE(lead(e.transaction_id) OVER (), e.transaction_id + 1) AS filter_tid, e.table_operation FROM pgmemento.table_event_log e LEFT JOIN pgmemento.row_log r ON r.event_key = e.event_key WHERE e.table_name = 'table_A' AND e.schema_name = 'public' AND (r.audit_id = 10 OR e.op_id IN (1,11,12,2,21,22)) AND e.transaction_id > ( SELECT min(lower(txid_range)) FROM pgmemento.audit_table_log WHERE log_id = 1 // public.table_A ) ORDER BY e.transaction_id, e.id ) i, LATERAL ( SELECT * FROM pgmemento.restore_record(i.first_tid, i.filter_tid, 'table_A', 'public', 10, TRUE) AS (log JSONB) ) j; ``` _**Note:**_ In the result of the tuple evolution the version you see in the JSONB log column, represents the version **BEFORE** this event occured. So, in case of an INSERT it will be empty. In the change view you'll see the version **AFTER** the event happend. Thus, for INSERT you see the inserted tuple. ## Multiple version for all rows Use `pgmemento.restore_recordsets` to retrieve all versions of all rows that have changed during the given transaction ID frame. Be careful, as this can reproduce the entire history of the given table. Better use it only along with smaller transaction ID windows.