PGHist

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

-- Create schema and tables
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);

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


-- Change data
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 all changes, first three columns provide chronological
select * from example.invoice_changes() order by 1,2,3;

-- Select all changes 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 changes 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;

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

-- Create function that returns all columns, including unnecessary
create or replace function example.invoice_changes_ui_simple(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;
$$;
select * from example.invoice_changes_ui_simple(12);

-- Create type with only necessary columns for the UI and cast function to it,
-- type is created only once (as a rule, there is only one history view form per project) 
create type example.table_change_ui as (
  timestamp timestamptz,
  operation_name varchar,
  db_user_name varchar,
  row_desc text,  
  column_comment varchar,      
  value_old_desc text,      
  value_new_desc text
);
create or replace function example.table_change_ui_cast(tc pghist.table_change) returns example.table_change_ui language plpgsql as $$
begin
  return (tc.timestamp,tc.operation_name,tc.db_user_name,tc.row_desc,tc.column_comment,tc.value_old_desc,tc.value_new_desc)::example.table_change_ui; 
end; $$;
create cast(pghist.table_change as example.table_change_ui) with function example.table_change_ui_cast as assignment;

-- Create function that returns only necessary columns
create or replace function example.invoice_changes_ui(id int) returns setof example.table_change_ui language sql security definer as $$
  select tc::pghist.table_change::example.table_change_ui from ( 
    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
  ) tc;  
$$;
select * 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;

-- postgres grant privileges on pghist schema and pghist.hist_enable procedure to developer_1
-- all privileges in pghist_grants.sql file
grant usage on schema pghist to developer_1;
grant execute on procedure pghist.hist_enable(name) to developer_1;


-- developer_1 create table, enable history, grant privileges on example table and example_changes function to user_1
create table example(
  id int primary key,
  name varchar(20),
  number numeric(10,2),
  date date
);

call pghist.hist_enable('example');
  
grant select,insert,update on example to user_1;
grant execute on function example_changes to user_1;


-- user_1 change data and view changes 
insert into example values (1, 'Example', 10, current_date);
update example set number=20, date=date-1;

select * from example_changes() order by 1,2,3;

-- When trying to select log, user_1 receives an error
-- SQL Error [42501]: ERROR: permission denied for view example_hist 
select * from example_hist;

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

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

select * from example.invoice_changes() order by 1,2,3;
select * from example.invoice_changes(insert_detail=>true, columns_immutable=>true) order by 1,2,3; 

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

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;

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; 
$$;
select * 
  from example_invoice_at_timestamp i
  join example_customer_at_timestamp c on c.id=i.customer_id;

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

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

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

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

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;

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;