default.rst

Parameters

Name	Type	Mode
schema_name	text	IN
table_name	text	IN
auto_sync	boolean	IN

Definition

declare
    trigger_name text;
    versioning_function text;
    update_trigger_name text;
    history_exists boolean;
    sync_result jsonb;
begin
    trigger_name := setup_temporal_table.table_name || '_versioning_trigger';
    update_trigger_name := setup_temporal_table.table_name || '_update_if_changed_trigger';
    versioning_function := 'versioning';

-- Check if history table exists
    select exists (
        select 1 from information_schema.tables t
        where t.table_schema = setup_temporal_table.schema_name
        and t.table_name = setup_temporal_table.table_name || '_history'
    ) into history_exists;

-- Add the system period column if it doesn't exist
    execute format(
        'alter table %I.%I add column if not exists sys_period tstzrange not null default tstzrange(current_timestamp, null);',
        setup_temporal_table.schema_name, setup_temporal_table.table_name
    );

-- Create the history table if it doesn't exist
    if not history_exists then
        execute format(
            'create table %I.%I (like %I.%I excluding constraints excluding indexes);',
            setup_temporal_table.schema_name, setup_temporal_table.table_name || '_history',
            setup_temporal_table.schema_name, setup_temporal_table.table_name
        );
        raise notice 'Created history table %.%_history', setup_temporal_table.schema_name, setup_temporal_table.table_name;
    elsif auto_sync then
        -- Sync schema if table exists and auto_sync is enabled
        sync_result := sync_temporal_table_schema(setup_temporal_table.schema_name, setup_temporal_table.table_name);
        raise notice 'Auto-sync result: %', sync_result::text;
    end if;

-- Drop existing triggers if they exist (for idempotency)
    execute format(
        'drop trigger if exists %I on %I.%I;',
        trigger_name, setup_temporal_table.schema_name, setup_temporal_table.table_name
    );

execute format(
        'drop trigger if exists %I on %I.%I;',
        update_trigger_name, setup_temporal_table.schema_name, setup_temporal_table.table_name
    );

-- Create the versioning trigger
    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, setup_temporal_table.schema_name, setup_temporal_table.table_name, versioning_function,
        setup_temporal_table.schema_name || '.' || setup_temporal_table.table_name || '_history'
    );

-- Create the update trigger that only updates if changed
    execute format(
        'create trigger %I before update on %I.%I
        for each row execute function update_if_changed();',
        update_trigger_name, setup_temporal_table.schema_name, setup_temporal_table.table_name
    );

raise notice 'Temporal tables setup completed for %.% and history table %_history',
        setup_temporal_table.schema_name, setup_temporal_table.table_name, setup_temporal_table.table_name;
end;

setup_temporal_table(schema_name text, table_name text, auto_sync boolean DEFAULT false)

Description

Parameters

Definition