PGHist

-- Создаем таблицу
create table example(
  id int primary key,
  name varchar(20),
  number numeric(10,2),
  date date
);

-- Включаем ведение истории
call pghist.hist_enable('example');

-- Изменяем данные 
insert into example values (1, 'Пример', 10, current_date);
update example set number=20, date=date-1;

-- Получаем лог изменений по строкам
select * from example_hist;

-- Получаем изменения по полям
select * from example_changes();

-- Получаем таблицу в прошлом
select * from example_at_timestamp(now()-interval '10 second');

-- drop table example cascade;

-- Create schema and table, enable history, change data 
drop schema if exists example cascade;
create schema example;

create table example.document(
  id int primary key,
  number varchar(10),
  amount numeric(10,2)
);

call pghist.hist_enable('example', 'document');

insert into example.document values (11, '#10', 100);
insert into example.document values (12, '#20', 200);
update example.document set number='#20/2',amount=210 where id=12;
update example.document set amount=220 where id=12;
delete from example.document where id=11;

-- Select full info
select * from example.document_hist;

-- Select partial info by id
select hist_timestamp,hist_operation,hist_db_user,amount_old 
  from example.document_hist
  where id=12 and (hist_operation!='UPDATE' or 'amount'=any(hist_update_columns))
  order by hist_statement_id;

-- Select partial info with current values
select * from example.document_hist
union all 
select null,null,null,'[CURRENT_VALUES]',null,null,null,null,null,null,* from example.document
order by id,1

-- Create extended view and grant select on it to developer
-- (alternatively to 'grant select on all tables in schema pghist to developer');  
select *
  from pghist.hist_data$example_document h  
  join pghist.hist_statement s on s.id = h.hist_statement_id
  join pghist.hist_query q on q.hash = s.query_hash
  join pghist.hist_transaction t on t.id = s.transaction_id
  order by h.hist_statement_id, h.id;
 
create or replace view example.document_hist_ext as  
  select h.id,t.timestamp_commit,t.db_client_addr,q.text query_text
    from pghist.hist_data$example_document h  
    join pghist.hist_statement s on s.id = h.hist_statement_id
    join pghist.hist_query q on q.hash = s.query_hash
    join pghist.hist_transaction t on t.id = s.transaction_id
    order by h.hist_statement_id, h.id;
   
select * from example.document_hist_ext where id=12;   

grant select on example.document_hist_ext to developer_1;      

-- Developer create schema and tables, enable history, change default desc, grant privileges to user
drop schema if exists example cascade;
create schema example;

create table example.customer(
  id int primary key,
  name varchar(100) not null
);
insert into example.customer values (1,'Horns'),(2,'Hooves');

create table example.invoice(
  id int primary key,  
  number varchar(10),
  date date,  
  customer_id int references example.customer(id),
  amount numeric(20,2) 
);
comment on table example.invoice is 'Invoice';
comment on column example.invoice.id is 'Identifier';
comment on column example.invoice.number is 'Number';
comment on column example.invoice.date is 'Date';
comment on column example.invoice.customer_id is 'Сustomer';
comment on column example.invoice.amount is 'Amount';

create table example.product(
  id int primary key,
  name varchar(100) not null,
  code varchar(10) not null
);

create table example.invoice_product(
  id serial primary key,
  invoice_id int references example.invoice(id),   
  product_id int references example.product(id),  
  quantity int,
  color char(1) check (color in ('R','G','B'))  
);
comment on table example.invoice_product is 'Product of invoice';
comment on column example.invoice_product.id is 'Identifier';
comment on column example.invoice_product.invoice_id is 'Invoice';
comment on column example.invoice_product.product_id is 'Product';
comment on column example.invoice_product.quantity is 'Quantity';
comment on column example.invoice_product.color is 'Color';
create index on example.invoice_product(invoice_id);

grant usage on schema example to user_1;  
grant select,insert,update,delete on example.invoice,example.product,example.invoice_product to user_1;

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

grant execute on function example.invoice_changes,example.invoice_product_changes to user_1;

-- User fills in tables, looks changes
insert into example.invoice values (12,'#20', current_date, 1, 120.00);
update example.invoice set customer_id=2 where id=12;
insert into example.product(id,name,code) values (101,'Pensil','030'),(102,'Notebook','040');
insert into example.invoice_product(id, invoice_id, product_id, quantity, color) values (1,12,101,1000,'R'),(2,12,101,10,'G');

do $$
begin
  update example.invoice_product set quantity=quantity+1,color='B' where id=1;
  delete from example.invoice_product where id=2;
  update example.invoice set amount=150 where id=12;
end; 
$$;

-- Select full data, first three columns provide chronological
select * from example.invoice_changes() order by 1,2,3;

-- Select full data with column id (immutable), insert detail by columns  
select * from example.invoice_changes(insert_detail=>true, columns_immutable=>true) order by 1,2,3; 

-- Select partial data by id
select * from example.invoice_changes('id=12') order by 1,2,3;

-- Select changes in two related tables, fast execution provides index invoice_product(invoice_id)
select * from example.invoice_changes('id=$1',12)
union all
select * from example.invoice_product_changes('invoice_id=$1', 12)
order by 1,2,3

-- Developer create function that displays information in custom format for user
create or replace function example.invoice_changes_ui(id int) returns setof pghist.table_change language sql security definer as $$
  select * from example.invoice_changes('id=$1',id,insert_detail=>true)
  union all
  select * from example.invoice_product_changes('invoice_id=$1', id)
  order by 1,2,3;
$$;

-- Set description expression for columns row_desc and value_desc   
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 $$);

-- Replace function for column db_user_name
create or replace function example.db_user_name(db_user name) returns varchar language plpgsql as $$ 
begin 
  return '['||db_user||']';                                        
end; $$;

call pghist.hist_column_custom_function('db_user_name', 'example.db_user_name');

-- Select changes for user interface 
select * from example.invoice_changes_ui(12);
select timestamp,operation_name,db_user_name,row_desc,column_comment,value_old_desc,value_new_desc from example.invoice_changes_ui(12);

--  Create schema and tables, enable history
drop schema if exists example cascade;
create schema example;

create table example.customer(
  id int primary key,
  name varchar(100) not null
);
insert into example.customer values (1,'Horns'),(2,'Hooves');

create table example.document(
  id int primary key,
  number varchar(10),
  date date
);

create table example.invoice(
  primary key (id),
  customer_id int references example.customer(id),
  amount numeric(20,2)
) inherits (example.document);
create index on example.invoice(customer_id);

call pghist.hist_enable('example', 'customer');
call pghist.hist_enable('example', 'document');
call pghist.hist_enable('example', 'invoice');

-- Enter data into tables   
insert into example.document values (11,'#10', current_date);
insert into example.invoice  values (12,'#20', current_date,   1, 120.00);
insert into example.invoice  values (13,'#30', current_date-1, 2, 130.00);

-- Select data in past
select * from example.document_at_timestamp(now()-interval '10 second');

-- Erroneous update and recovery
update example.invoice set customer_id=2,amount=300 where date=current_date;

update example.invoice i
    set amount = h.amount
  from example.invoice_at_timestamp('2024-04-06 10:00:00') h
  where i.id = h.id
    and i.date=current_date;

-- Сombination log and versioning   
select * 
  from example.invoice_at_timestamp('2024-04-06 10:00:00')
  where id in (
          select id from example.invoice_hist
            where hist_timestamp>'2024-04-06 10:00:00'
              and hist_db_user=current_user
        );

-- Сomplex query in past        
select * 
  from example.invoice i
  join example.customer c on c.id=i.customer_id;
 
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; 
$$;
 
select * 
  from example_invoice_at_timestamp i
  join example_customer_at_timestamp c on c.id=i.customer_id;