# PostgreSQL extension temporal_tables 初步介紹

## 基本安裝
[github網址](https://github.com/arkhipov/temporal_tables)

git clone https://github.com/arkhipov/temporal_tables
cd temporal_tables
make
make install

create extension temporal_tables with schema public;
commit;

dx temporal_tables
             List of installed extensions
      Name       | Version | Schema |   Description
-----------------+---------+--------+-----------------
 temporal_tables | 1.2.0   | public | temporal tables


\dx+ temporal_tables
       Objects in extension "temporal_tables"
                 Object description
----------------------------------------------------
 function set_system_time(timestamp with time zone)
 function versioning()

## 基本的測試

create table employees (
  name text not null primary key
, dept text
, salary int
);


alter table employees
  add column sys_period tstzrange not null;

create table employees_history (like employees);

-- 使用 versioning() 函數
create trigger versioning_employees
before insert or update or delete
on employees for each row
execute function versioning('sys_period', 'employees_history', true);

commit;

insert into employees values 
('小趙', '櫃檯', 50000),
('小錢', '二廚', 60000),
('老孫', '大廚', 90000);

commit;

select * from employees;

 name | dept | salary |             sys_period
------+------+--------+------------------------------------
 小趙 | 櫃檯 |  50000 | ["2021-05-22 08:44:25.572207+08",)
 小錢 | 二廚 |  60000 | ["2021-05-22 08:44:25.572207+08",)
 老孫 | 大廚 |  90000 | ["2021-05-22 08:44:25.572207+08",)
(3 rows)

-- 觀察到 trigger 使用了 versioning() , 紀錄了timestamptz

update employees
   set salary = 55000
 where name = '小趙';

commit;

select *
  from employees_history;

-[ RECORD 1 ]-----------------------------------------------------------------
name       | 小趙
dept       | 櫃檯
salary     | 50000
sys_period | ["2021-05-22 08:44:25.572207+08","2021-05-22 08:45:21.505547+08")

delete from employees where name = '小錢';

commit;

select *
  from employees_history;

-[ RECORD 1 ]-----------------------------------------------------------------
name       | 小趙
dept       | 櫃檯
salary     | 50000
sys_period | ["2021-05-22 08:44:25.572207+08","2021-05-22 08:45:21.505547+08")
-[ RECORD 2 ]-----------------------------------------------------------------
name       | 小錢
dept       | 二廚
salary     | 60000
sys_period | ["2021-05-22 08:44:25.572207+08","2021-05-22 08:45:30.713344+08")

## 結論
今天先對 PostgreSQL 此一 extension 做一個初步簡單的測試介紹.
主要是介紹 temporal table 的概念.