Custom Temporal Tables in PostgreSQL

floki

Oct 10, 2023

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

Description

It will add two new functions in the public schema:

1) function 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) function 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']);

select init_temporal_tables(array['public']);

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;
$$;

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;
$$;

To receive notifications about new posts and updates, consider subscribing to my LinkdIn page:
vb-software linkedin

You will receive notifications about new posts on your LinkedIn feed.

Comments