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


Процедура включает ведение истории изменений для указанной таблицы, создает таблицу истории, триггеры и функции получения данных

Параметры:
schema-схема таблицы (необязательно)
table_name-имя таблицы
master_table_schema-схема мастер-таблицы (необязательно)
master_table_name-имя мастер-таблицы (необязательно)

Примеры вызова:
call pghist.hist_enable('document');
call pghist.hist_enable('example', 'invoice');
call pghist.hist_enable('example', 'invoice_product', 'example', 'invoice');

[schema].[table]_hist


Представление содержит список изменений по строкам, создается при включении ведения истории.
Сортируется по первым двум столбцам, что обеспечивает хронологию по времени

Столбцы:
hist_statement_num-глобальный номер SQL-выражения
hist_row_num-номер строки в наборе данных, который изменяет SQL-выражения
hist_timestamp-дата-время начала выполнения SQL-выражения
hist_operation-операция: INSERT,UPDATE,DELETE,TRUNCATE
hist_update_columns-список обновленных столбцов для операции UPDATE
hist_db_user-пользователь базы данных
hist_app_user-пользователь приложения, определяется как current_setting('app.user')
hist_application_name-имя приложения
hist_query_text-текст SQL-запроса
hist_statement_id-ID SQL-выражения, ссылается на таблицу pghist.hist_statement
[primary key column(s)]-значение первичного ключа (можно быть несколько)
[column(s) of foreign key on master table]-значение внешного ключа на мастер-таблицу (необязательно, можно быть несколько)
[column...]_old-старое значение для каждого столбца таблицы [schema].[table]

Примеры вызова:
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, cascade boolean)


Функция возвращает изменения в таблице (setof pghist.table_change), создается при включении ведения истории.
Сортировка по первым трем столбцам обеспечивает хронологию по времени, для получения связанных данных из нескольких таблиц рекомендуется использовать оператор union all

Параметры:
where_clause-условие на таблицу (необязательный)
where_param-значение параметра в where_clause (необязательный)
columns_immutable-возвращать значения неизменямых столбцов (по умолчанию false)
insert_detail-детализировать операцию INSERT по столбцам (по умолчанию false)
cascade-учитывать унаследованные таблицы (по умолчанию true)

Столбцы возвращаемого набора данных:
statement_num-глобальный номер SQL-выражения
row_num-номер строки в наборе данных, который изменяет SQL-выражения
column_num-номер столбца в строке
timestamp-дата-время операции
operation-операция: INSERT,UPDATE,DELETE,TRUNCATE
operation_name-наименование операции, задается переопределяемой функцией pghist.hist_custom_operation_name
column_name-имя столбца
column_comment-комментарий столбца
value_old-старое значение
value_old_desc-описание старого значения, можно переопредилить через процедуру pghist.hist_expression_value_desc
value_new-новое значение
value_new_desc-описание нового значение, можно переопредилить через процедуру pghist.hist_expression_value_desc
row_desc-описание строки, можно переопредилить через процедуру pghist.hist_expression_row_desc
db_user-пользователь базы данных
db_user_name-имя пользователя базы данных, задается переопределяемой функцией pghist.hist_custom_db_user_name
app_user-пользователь приложения, задается переопределяемой функцией pghist.hist_custom_app_user
app_user_name-имя пользователя приложения, задается переопределяемой функцией pghist.hist_custom_app_user_name
schema-схема
table_name-имя таблицы
table_comment-комментарий таблицы

Примеры вызова:
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)


Функция создает временную таблицу [schema]_[table]_at_timestamp как копию исходной таблицы по состоянию на момент времени и возращает ее

Параметры:
transaction_timestamp-дата-время завершения транзации, по умолчанию current_setting('pghist.at_timestamp')
cascade-учитывать унаследованные таблицы (по умолчанию true)

Примеры вызова:
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)


Процедура выключает ведение истории изменений для указанной таблицы, удаляет необходимые объекты

Параметры:
schema-схема таблицы (необязательный)
table_name-имя таблицы

Примеры вызова:
call pghist.hist_disable('document');
call pghist.hist_disable('example', 'invoice');

SQL-выражения описаний


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)

Процедуры устанавливают выражение для описания строки/значения поля, в выражении доступна переменная $1 c строкой/значением поля


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)

Функции возвращают текущее выражение для описания строки/значения поля


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)

Функции возвращают выражение по умолчанию для описания строки/значения поля. Для строки возращается комментарий таблицы с первичным ключом, для столбца, являющегося внешним ключом, - первое текстовое поля связанной таблицы.


Параметры:
schema-схема таблицы
table-имя таблицы
column_name-имя столбца
expression-выражение, null отменяет сохраненное описание

Примеры вызова:
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');

Функции кастомизации pghist.hist_custom_[column]


pghist.hist_custom_operation_name()-наименование операции
pghist.hist_custom_db_user_name(db_user)-имя пользователя базы данных
pghist.hist_custom_app_user()-пользователь приложения
pghist.hist_custom_app_user_name(app_user)-имя пользователя приложения
pghist.hist_custom_app_client_addr()-IP адрес клиентского приложения
pghist.hist_custom_app_client_hostname()-Имя хоста(компьютера) клиентского приложения

Функции кастомизации столбцов, представляют собой "заглушки" для переопределенния под конкретное приложение.
Создаются при первичной установке pghist и при обновлении не перезаписываются.
Рекомендуется редактировать в SQL-менеджере (например, pgAdmin или DBeaver)

Примеры переопределения:
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; $$;

Таблица истории pghist.hist_[schema]_[table]


При включении истории дополнительно к основной таблице создается таблица истории, разработчику (владельцу основной таблицы) доступна через представление [schema].[table]_hist

Столбцы:
hist_statement_id-ID SQL-выражения, ссылается на таблицу pghist.hist_statement
hist_row_num-номер строки в наборе данных, который изменяет SQL-выражения
hist_update_columns-список обновленных столбцов для операции UPDATE
[primary key column(s)]-значение первичного ключа (можно быть несколько)
[column(s) of foreign key on master table]-значение внешного ключа на мастер-таблицу (необязательно, можно быть несколько)
[column...]_old-старое значение для каждого столбца таблицы [schema].[table]

Пример использования:
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%'

Общие таблицы


pghist.hist_transaction-транзакции
pghist.hist_query-SQL-запросы
pghist.hist_statement-SQL-выражения

При установке создаются таблицы, которые хранят общую информацию по изменениям: дата-время транзакции и SQL-выражения, операция, SQL-запрос, пользователь, сессия и т.д.
Таблица истории pghist.hist_[schema]_[table] по полю statement_id ссылается на pghist.hist_statement, которая в свою очередь ссылается на pghist.hist_transaction и pghist.hist_query. По умолчанию разработчикам (владельцам основных таблицы) общие таблицы недоступны, при необходимости доступ выдается вручную.

Пример использования:
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()


Функция возвращает версию инструмента

pghist.hist_sql_log


Все команды, выполняемые утилитой, записываются в лог-таблицу pghist.hist_sql_log

Примеры использования:
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;