Temporal table in PostgreSQL

[vbilopav]

The following script will add support for temporal tables in PostgreSQL.

It will add two new functions in the public schema:

1. temporal_table_trigger - trigger function that inserts temporal data.

This function will create a new schema (source schema plus __history) and a new table with temporal data in it.

The new table will have the exact same schema without keys and constraints with one extra field - data_valid_to timestamptz not null default now();

2. init_temporal_tables

This function initiates the entire temporal tables system by creating trigger temporal_table_trigger on each tables It accepts an array of schema names we want to have temporal tables.

To call this function for the public schema:

select init_temporal_tables(array['public']);

The script:

create or replace function temporal_table_trigger()
returns trigger
language plpgsql
as
$$
declare
    _schema text;
    _table text;
begin   
    _schema = TG_TABLE_SCHEMA::text || '__history';
    _table = TG_TABLE_NAME::text;
    if not exists(  
            select 1 from information_schema.tables 
            where table_schema = _schema and table_name = _table
    ) then
        execute format(
            $sql$
            create schema if not exists %1$I;
            create table %1$I.%2$I as
            select * from %3$I.%4$I limit 0;
            alter table %1$I.%2$I add column data_valid_to timestamptz not null default now();
            $sql$, 
            _schema, _table, TG_TABLE_SCHEMA::text, TG_TABLE_NAME::text
        );
    end if;
    
    execute format(
        $sql$
        insert into %1$I.%2$I
        select $1.*;
        $sql$, 
        _schema, _table
    ) using old;
        
    return old;
end;
$$;

create or replace function init_temporal_tables(
    _schemas text[],
    _dry_run boolean = false
)
returns void
language plpgsql
as
$$
declare
    _schema text;
    _table text;
    _cmd text;
    _trigger text = 'temporal_table_trigger';
begin   
    for _schema, _table in (
        select tbl.table_schema, tbl.table_name 
        from 
            information_schema.tables tbl
            left join information_schema.triggers tr 
            on tbl.table_schema = tr.event_object_schema 
                and tbl.table_name = tr.event_object_table
                and tr.trigger_name = _trigger
        where 
            tr.event_object_table is null 
            and table_schema = any(_schemas) 
            and table_type = 'BASE TABLE'
    )
    loop
        
        _cmd = format(
            $sql$
            create or replace trigger %1$I 
            after delete or update on %2$I.%3$I 
            for each row execute procedure %1$I();
            $sql$, 
            _trigger, _schema, _table
        );
        
        raise info '%', replace(_cmd, '    ', '');
        
        if _dry_run is false then
            execute _cmd;
        end if;
        
    end loop;
end;
$$;