default.rst

Parameters

Name	Type	Mode
schema_name	text	IN
table_name	text	IN

Definition

declare
    col_record record;
    added_columns jsonb := '[]'::jsonb;
    column_info jsonb;
    col_def text;
begin
    -- Find columns in source table that don't exist in history table
    for col_record in
        select
            c.column_name,
            c.data_type,
            c.character_maximum_length,
            c.numeric_precision,
            c.numeric_scale,
            c.column_default,
            c.is_nullable,
            c.udt_name
        from information_schema.columns c
        where c.table_schema = sync_temporal_table_schema.schema_name
        and c.table_name = sync_temporal_table_schema.table_name
        and c.column_name not in (
            select h.column_name
            from information_schema.columns h
            where h.table_schema = sync_temporal_table_schema.schema_name
            and h.table_name = sync_temporal_table_schema.table_name || '_history'
        )
        -- Exclude system period column as it's managed separately
        and c.column_name != 'sys_period'
        order by c.ordinal_position
    loop
        -- Build column definition based on data type
        col_def := case
            when col_record.character_maximum_length is not null then
                col_record.data_type || '(' || col_record.character_maximum_length || ')'
            when col_record.numeric_precision is not null then
                col_record.data_type || '(' || col_record.numeric_precision ||
                coalesce(',' || col_record.numeric_scale, '') || ')'
            when col_record.data_type = 'ARRAY' then
                col_record.udt_name
            else
                col_record.data_type
        end;

-- Execute ALTER TABLE for the missing column
        execute format(
            'alter table %I.%I add column %I %s %s %s',
            sync_temporal_table_schema.schema_name,
            sync_temporal_table_schema.table_name || '_history',
            col_record.column_name,
            col_def,
            case when col_record.is_nullable = 'NO' then 'not null' else '' end,
            case
                when col_record.column_default is not null
                then 'default ' || col_record.column_default
                else ''
            end
        );

-- Build JSON object with column info
        column_info := jsonb_build_object(
            'column_name', col_record.column_name,
            'data_type', col_def,
            'nullable', col_record.is_nullable = 'YES',
            'has_default', col_record.column_default is not null
        );

-- Add to the list of added columns
        added_columns := added_columns || column_info;

raise notice 'Added column "%" (type: %) to %.%_history',
            col_record.column_name, col_def, sync_temporal_table_schema.schema_name, sync_temporal_table_schema.table_name;
    end loop;

if jsonb_array_length(added_columns) > 0 then
        raise notice 'Schema synchronization completed for %.%: % column(s) added',
            sync_temporal_table_schema.schema_name, sync_temporal_table_schema.table_name, jsonb_array_length(added_columns);
    else
        raise notice 'Schema synchronization completed for %.%: No new columns to add',
            sync_temporal_table_schema.schema_name, sync_temporal_table_schema.table_name;
    end if;

return jsonb_build_object(
        'table', sync_temporal_table_schema.schema_name || '.' || sync_temporal_table_schema.table_name,
        'columns_added', added_columns,
        'count', jsonb_array_length(added_columns)
    );
end;

sync_temporal_table_schema(schema_name text, table_name text)

Description

Parameters

Definition