The restore API also allows for persisting a restored recordset as either a VIEW (default), MATERIALIZED VIEW or TABLE. Therefore, use the pgmemento.restore_table_state function where you can also address a new target schema for storing the history. With the last flag parameter you choose to overrite already restored entities.
sql
SELECT
pgmemento.restore_table_state(
start_from_tid := 1,
end_at_tid := 10,
original_table_name := 'table_A',
original_schema_name := 'public',
target_schema_name := 'target_schema',
target_table_type := 'VIEW',
update_state := TRUE
);
Persist a whole schema
With the pgmemento.restore_schema_state state function you can restore all logged tables of a given schema. It will check the audit_table_log table to see which tables need to recreated for the requested transaction ID filter.
sql
SELECT
pgmemento.restore_table_state(
start_from_tid := 1,
end_at_tid := 10,
original_table_name := 'public',
target_schema_name := 'target_schema',
target_table_type := 'VIEW',
update_state := TRUE
);
Work with a past state
If past states were restored as tables they do not have primary keys or indexes assigned to them. References between tables are lost as well. If the user wants to work on the restored table or database state - like he would do with the production state - he can use the procedures pgmemento.pkey_table_state, pgmemento.fkey_table_state and
pgmemento.index_table_state. These procedures create primary keys, foreign keys and indexes on behalf of the recent constraints defined in the production schema.
Note: If table and/or database structures have changed fundamentally over time it might not be possible to recreate constraints and indexes as their metadata is not yet logged by pgMemento.