PGHist

pghist.hist_enable
[schema].[table]_hist
[schema].[table]_changes
[schema].[table]_at_timestamp
pghist.hist_disable
SQL expressions of descriptions
Customization functions
History table
Common tables
pghist.pghist_version
pghist.hist_sql_log

pghist.hist_enable(schema, table_name, master_table_schema, master_table_name, columns_excluded)

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)
columns_excluded	-	excluded columns (optional)

Examples:

call pghist.hist_enable('document');

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

call pghist.hist_enable('orm', 'myclass', columns_excluded => array['inserted_at','updated_at']);

[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

To customize and localize fields in a specific application, can be define custom functions.
Recommended to use the default function in SQL manager (such as pgAdmin or DBeaver), override it and save in application schema as different name.

Columns available for customization:

operation_name	-	operation name
db_user_name	-	database user name
app_user	-	application user
app_user_name	-	application user name
app_client_addr	-	client application IP address
app_client_hostname	-	client application host name

pghist.hist_column_custom_function(column_name name, custom_function name)

The procedure sets function to get column value

Parameters:

	column_name	-	column name
	custom_function	-	function name without parameters

pghist.hist_column_custom_function_current(column_name name)

The function returns current function to get column value

Parameter:

column_name

column name

Example:

select pghist.hist_column_custom_function_current('db_user_name');

create or replace function myapp.user_name(db_user name) returns varchar language plpgsql as $$ 
begin 
  return (select name from myapp.users where login=db_user);                                        
end; $$;

call pghist.hist_column_custom_function('db_user_name', 'myapp.user_name');

History table pghist.hist_data$[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_data$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
pghist.hist_table	-	history tables
pghist.hist_table_column	-	columns of history tables
pghist.hist_column_custom_function	-	custom functions of columns

On installation created tables that store common data (transaction date-time and SQL expressions, operation, SQL query, user, session, etc) and tables with settings. Modification of common tables is allowed using stored procedures. By default, developers (owners of main tables) do not have access to common tables; if necessary, privilegies are granted manually.

History table pghist.hist_data$[schema]_[table] by field statement_id refers to pghist.hist_statement, which in, in turn, refers to pghist.hist_transaction and pghist.hist_query.

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

select *
  from pghist.hist_table ht
  left join pghist.hist_table_column htc on htc.schema=ht.schema and htc.table_name=ht.name;

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;

History of table changes in PostgreSQL

PGHist

Documentation