pghist.hist_enable(schema name, table_name name)


Procedure enable keeping history of changes for table, create history table, triggers and functions that return historical data

Parameters:
schema-table schema (optional)
table_name-table name
master_table_schema-master table schema (optional)
master_table_name-master table name (optional)

Examples:
call pghist.hist_enable('document');
call pghist.hist_enable('example', 'invoice');
call pghist.hist_enable('example', 'invoice_product', 'example', 'invoice');

[schema].[table]_hist


View contains a list of changes by row, created when history keeping is enabled.
Sorted by first two columns to provide chronology

Columns:
hist_statement_num-common SQL statement number
hist_row_num-row number in dataset that modified SQL statement
hist_timestamp-SQL statement start date-time
hist_operation-operation: INSERT,UPDATE,DELETE,TRUNCATE
hist_update_columns-changed columns for UPDATE operation
hist_db_user-database user
hist_app_user-application user defined as current_setting('app.user')
hist_application_name-application name
hist_query_text-SQL query text
hist_statement_id-SQL statement ID referenced on pghist.hist_statement table
[primary key column(s)]-primary key value (can be several)
[column(s) of foreign key on master table]-foreign key value on master table (optional, can be several)
[column...]_old-old value for each column of table [schema].[table]

Examples:
select * from example.document_hist;
select * from example.document_hist where id=2;
select * from example.document_hist where hist_operation='DELETE';

[schema].[table]_changes(where_clause text, where_param anyelement, row_comment_clause text, cascade boolean)


Function return changes in the table (setof pghist.table_change), created when history keeping is enabled.
Sorting by the first three columns provides chronology by time, to get related data from multiple tables, recommended to use operator union all

Parameters:
where_clause-condition on table (optional)
where_param-value of the parameter in where_clause (optional)
columns_immutable-return values of immutable columns (default false)
insert_detail-detail INSERT operation by column (default false)
cascade-process inherited tables (default true)

Returned dataset columns:
statement_num-common SQL statement number
row_num-row number in dataset that modified SQL statement
column_num-column number in row
timestamp-operation date-time
operation-operation: INSERT,UPDATE,DELETE,TRUNCATE
operation_name-operation name, specified by the overridden function pghist.hist_custom_operation_name
column_name-column name
column_comment-column comment
value_old-old value
value_old_desc-description of old value, can be overridden by the procedure pghist.hist_expression_value_desc
value_new-new value
value_new_desc-description of new value, can be overridden by the procedure pghist.hist_expression_value_desc
row_desc-description of row, can be overridden by the procedure pghist.hist_expression_row_desc
db_user-database user
db_user_name-database user name, specified by the overridden function pghist.hist_custom_db_user_name
app_user-application user, specified by the overridden function pghist.hist_custom_app_user
db_user_name-application user name, specified by the overridden function pghist.hist_custom_app_user_name
schema-schema
table_name-table name
table_comment-table comment

Examples:
select * from example.document_changes();
select * from example.invoice_product_changes('id=$1',2)
  where column_name not in ('id','invoice_id')
  order by hist_id desc,column_pos

[schema].[table]_at_timestamp(transaction_start timestamptz, cascade boolean)


Function created a temporary table [schema]_[table]_at_timestamp as copy of the original table at point in time and returns it

Parameters:
transaction_timestamp-date-time of transaction completion, default current_setting('pghist.at_timestamp')
cascade-process inherited tables (default true)

Example:
select * from example.document_at_timestamp(now()-interval '1 hour');

do $$
begin
  perform set_config('pghist.at_timestamp', '2024-04-06 10:00:00', true);    
  perform example.invoice_at_timestamp();    
  perform example.customer_at_timestamp();
end; 
$$;

pghist.hist_disable(schema name, table_name name)


Procedure disable keeping history of changes for table, delete the necessary objects

Parameters:
schema-table schema (optional)
table_name-table name

Example:
call pghist.hist_disable('document');
call pghist.hist_disable('example', 'invoice');

SQL expressions of descriptions


pghist.hist_expression_row_desc(schema name, table_name name, expression varchar)
pghist.hist_expression_value_desc(schema name, table_name name, column_name name, expression varchar)

The procedures set expression to describe row or value of field.
Variable $1 with row/field value is available in expression


pghist.hist_expression_row_desc_current(schema name, table_name name)
pghist.hist_expression_value_desc_current(schema name, table_name name, column_name name)

Functions return current expression to describe row or value of field


pghist.hist_expression_row_desc_default(schema name, table_name name)
pghist.hist_expression_value_desc_default(schema name, table_name name, column_name name)

Functions return default expression to describe row or value of field.
For row, table comment with the primary key is returned.
For column that is foreign key, first text field of foreigned table is returned.


Parameters:
schema-table schema
table-table name
column_name-column name
expression-expression, null discards the saved description

Examples:
call pghist.hist_expression_row_desc('example', 'invoice', '''Invoice''');   
call pghist.hist_expression_row_desc('example', 'invoice_product', $$ 'Row #'||$1.id||' / '||(select name from example.product where id=$1.product_id) $$);
call pghist.hist_expression_value_desc('example', 'invoice_product', 'color', $$ case when $1='R' then 'Red' when $1='B' then 'Blue' when $1='G' then 'Green' else $1 end $$);

select pghist.hist_expression_row_desc_current('example', 'invoice_product');
select pghist.hist_expression_value_desc_default('example', 'invoice_product', 'color');

Customization functions pghist.hist_custom_[column]


pghist.hist_custom_operation_name()-operation name
pghist.hist_custom_db_user_name(db_user)-database user name
pghist.hist_custom_app_user()-application user
pghist.hist_custom_app_user_name(app_user)-application user name
pghist.hist_custom_app_client_addr()-client application IP address
pghist.hist_custom_app_client_hostname()-client application host name

Functions for customize columns for a specific application, сreated on initial installation of pghist and are not overwritten on update. Recommended to edit in a SQL manager (for example, pgAdmin or DBeaver)

Examples:
create or replace function pghist.hist_custom_db_user_name(db_user name) returns varchar language plpgsql as $$
begin 
  return (select name from app.users where login=db_user);
end; $$;      

create or replace function pghist.hist_custom_app_client_addr() returns inet language plpgsql as $$ 
begin 
  return (select ip_address from app.session where id=current_setting('app.session_id', true));
end; $$;

History table pghist.hist_[schema]_[table]


History table is created in addition to the main table, developer (owner of main table) has access through the view [schema].[table]_hist

Столбцы:
hist_statement_id-SQL statement ID referenced on pghist.hist_statement table
hist_row_num-row number in dataset that modified SQL statement
hist_update_columns-changed columns for UPDATE operation
[primary key column(s)]-primary key value (can be several)
[column(s) of foreign key on master table]-foreign key value on master table (optional, can be several)
[column...]_old-old value for each column of table [schema].[table]

Example:
select *
  from pghist.hist_example_invoice h
  join pghist.hist_statement s on s.id=h.hist_statement_id
  join pghist.hist_query q on q.hash=s.query_hash
  where q.text like 'do%begin%'

Common tables


pghist.hist_transaction-transactions
pghist.hist_query-SQL queries
pghist.hist_statement-SQL statements

On installation created tables that store common data: transaction date-time and SQL expressions, operation, SQL query, user, session, etc.
History table pghist.hist_[schema]_[table] by field statement_id refers to pghist.hist_statement, which in, in turn, refers to pghist.hist_transaction and pghist.hist_query. By default, developers (owners of main tables) do not have access to common tables; if necessary, privilegies are granted manually

Example:
select *
  from pghist.hist_statement s
  join pghist.hist_transaction t on t.id=s.transaction_id
  join pghist.hist_query q on q.hash=s.query_hash
  where t.timestamp_commit between '2024-04-06 10:00:00' and '2024-04-06 11:00:00';

pghist.pghist_version()


The function returns tool version

pghist.hist_sql_log


All executed commands logging in table pghist.hist_sql_log

Examples:
select * from pghist.hist_sql_log where schema='example' and table_name='document' order by id;
select * from pghist.hist_sql_log l
  join pghist.hist_transaction t on t.id=l.transaction_id
  where schema='example' and table_name='document'
  order by 1 desc;