Building blocks for generic history-keeping in Postgres.

history_stuff.sql

/*
Replace "your_schema" with whatever schema is appropriate in your environment.
It is possible to use "public"... but you shouldn't!
*/


/*
Function to stamp a "modified" timestamp. Adjust the name to suit your environment,
but that name is hard-coded so it is assumed that you only use _one_ such name.

Bind to a table like so:
  create trigger z_stamp_modified
  before insert or update
  on your_schema.your_table_name
  for each row
  execute procedure your_schema.f_stamp_modified() ;
  
Remember, trigger names are table-local so you can use the same name everywhere.
Triggers within a phase are executed in order by name. The "z_" prefix is a hacky/imperfect
way of saying "this one should be last". You can use any name you like.
*/
create function your_schema.f_stamp_modified()
returns trigger
as $$
declare
begin
    NEW.modified = now();
    return NEW;
end;
$$ language plpgsql;


/*
Generic history table template.

You CAN use this as a single history table to record all changes to any table in your system
BUT YOU SHOULD NOT DO THAT. You absolutely want to have a separate history table for each real table.
Using table inheritance lets you pretend they're all one at query time if you wish.

Create individual history tables like so:
  create table your_schema.foo_history () inherits(your_schema.base_history) ;
  
You will probably want to index "created" and "pk_value".

Field meanings...

relid: 
  The system OID of the tracked table. In practice this turned out to be less useful than I thought it would be, and can
  probably be omitted. It costs little though, and provides some potentially interesting optionality. (Remember a relid can
  be transformed into a name with ::relname).
  
dml_type:
  I for insert, U for update, D for delete. Some folks find this cryptic but one letter is all you need.
  
txid:
  The ID of the transaction in which a change occurred. Very useful for identifying changes that were made together. These
  values may eventually wrap around so be cautious about comparing over long spans of time.
  
application_name:
  The application name value supplied by the client, e.g. psql looks for this in the PGAPPNAME env var
  
db_session_user:
  The name of the database user
  
inet_client_addr:
  The IP address of the client
  
old_row_data:
  The row as it was before the change, as serialized by row_to_json(). Null for inserts.
  
new_row_data:
  The row as it is after the change, as serialized by row_to_json(). Null for deletes.
  
pk_value:
  The value of the affected row's primay key field. Requires passing the name of that field in the trigger binding.
*/
create table your_schema.base_history
(
  hist_id                        bigserial not null primary key
, created                        timestamptz default now() not null
, relid                          oid
, dml_type                       char(1) not null
, txid                           bigint default txid_current() not null
, application_name               text default current_setting('application_name')
, db_session_user                text default session_user not null
, inet_client_addr               inet default inet_client_addr()
, pg_backend_pid                 int default pg_backend_pid()
, old_row_data                   json
, new_row_data                   json
, pk_value                       bigint
);

--this check constraint is more for documentation purposes, and can be omitted if you wish
alter table public.base_history
add constraint chk_basehistory_dmlrowdata
check (   (dml_type = 'I' and old_row_data is null     and new_row_data is not null)
       or (dml_type = 'U' and old_row_data is not null and new_row_data is not null)
       or (dml_type = 'D' and old_row_data is not null and new_row_data is null    )
      )
;


/*
Generic history trigger function.

This function takes one optional argument which is the name of the tracked table's primary key field, which must be
a bigint (or castable to a bigint). If that argument is not supplied, the function will still work, but "pk_value" in the 
history table will not be populated. 

This can all be adapted to e.g. UUID PKs, but that is left as an exercise for the reader. If you use a naming standard 
such as where all PKs are named "ID", this function can be simplified considerably. I do not favor that strategy so again
that adaptation is left as an exercise.

The names of history tables are assumed to be "_history" as a suffix to the tracked table, e.g. the history for table
"foo" is "foo_history", in the same schema. This code can easily be adapted to use a different suffix, a prefix, a different
schema, take the history table name as an argument, or whatever you like.

Bind to a table like so:
  create trigger z_record_history
  after insert or update or delete
  on your_schema.your_table_name
  for each row
  execute procedure your_schema.f_generic_history('your_table_name_id') ;
*/
create function your_schema.f_generic_history()
returns trigger
as $$
declare
    cc_dml_type constant char(1) := substring(TG_OP from 1 for 1) ;
    vc_insert_sql constant text := 'insert into your_schema.'||TG_TABLE_NAME||'_history ( relid, dml_type, old_row_data, new_row_data, pk_value ) values ( $1, $2, $3, $4, $5 )' ;
    vc_pk_sql_template constant text := 'select ($1).%I' ;
    vc_pk_column_name constant varchar(64) := TG_ARGV[0] ;
    n_pk_value bigint := null ;
    v_message text ;
    v_detail text ;
    v_hint text ;
begin
    if TG_OP = 'INSERT' then
        if vc_pk_column_name is not null
        then
            execute format(vc_pk_sql_template, vc_pk_column_name) into n_pk_value using NEW ;
        end if;

        execute vc_insert_sql using TG_RELID, cc_dml_type, null::json, row_to_json( NEW ), n_pk_value ;

    elsif (TG_OP = 'UPDATE' and OLD is distinct from NEW)
    then
        if vc_pk_column_name is not null
        then
            execute format(vc_pk_sql_template, vc_pk_column_name) into n_pk_value using NEW ;
        end if;

        execute vc_insert_sql using TG_RELID, cc_dml_type, row_to_json( OLD ), row_to_json( NEW ), n_pk_value ;
    elsif TG_OP = 'DELETE'
    then
        if vc_pk_column_name is not null
        then
            execute format(vc_pk_sql_template, vc_pk_column_name) into n_pk_value using OLD ;
        end if;

        execute vc_insert_sql using TG_RELID, cc_dml_type, row_to_json( OLD ), null::json, n_pk_value ;
    end if ;

    return null ;

exception
    when others then
        get stacked diagnostics v_message := MESSAGE_TEXT
                              , v_detail := PG_EXCEPTION_DETAIL
                              , v_hint := PG_EXCEPTION_HINT ;
        raise warning 'SQLSTATE % in f_generic_history(%) on table %.%; MESSAGE=%; DETAIL=%; HINT=%', SQLSTATE, TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME, v_message, v_detail, v_hint ;
        return null ;
end ;
$$ language plpgsql;