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 renamed. 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 behavior.

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 occurred 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 occurred. So, in case of an INSERT it will be empty. In the change view you'll see the version AFTER the event happened. 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.