default
.rst
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
setup_temporal_table(schema_name text, table_name text)
Parameters
Name
Type
Mode
schema_name
text
IN
table_name
text
IN
Definition
declare trigger_name text; versioning_function text; update_trigger_name text; begin trigger_name := table_name || '_versioning_trigger'; update_trigger_name := table_name || '_update_if_changed_trigger'; versioning_function := 'versioning'; -- add the system period column execute format( 'alter table %I.%I add column sys_period tstzrange not null default tstzrange(current_timestamp, null);', schema_name, table_name ); -- create the history table execute format( 'create table %I.%I (like %I.%I excluding constraints);', schema_name, table_name || '_history', schema_name, table_name ); -- create the versioning trigger on the table execute format( 'create trigger %I before insert or update or delete on %I.%I for each row execute procedure %I(''sys_period'', %L, true);', trigger_name, schema_name, table_name, versioning_function, schema_name || '.' || table_name || '_history' ); -- create the update trigger on the table that only updates the row if it's changed execute format( 'create trigger %I before update on %I.%I for each row execute function update_if_changed();', update_trigger_name, schema_name, table_name ); raise notice 'temporal tables setup completed for % and history table %', schema_name || '.' || table_name, schema_name || '.' || table_name || '_history'; end;