To restore a complete tuple use the function pgmemento.restore_record. It requires the following inputs:

  1. start_from_tid - transaction ID from where you want to start your search
  2. end_at_tid - upper transaction ID filter that defines the version of the tuple
  3. table_name - name of the table the record belongs to
  4. schema_name - name of the schema the table belongs to
  5. aid - the audit_id that defines which row you are intrested in
  6. 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. ```sql SELECT pgmemento.restore_record_definition(10, 'table_A', 'public', 'audit_trail_id');

restore_record_definition

AS (id integer, column_B text, column_C character, audit_trail_id bigint) ```

The returned text can be attached to the following query: sql SELECT * FROM pgmemento.restore_record(1, 10, 'table_A', 'public', 555) AS (id integer, column_B text, column_C character, audit_trail_id bigint);

Returning JSONB

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.

sql SELECT * FROM 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 pgmemento.restore_record internally.

sql 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.