--수정사항, 2016.1116 : check & 트리거를 사용하면 메인 테이블에 Insert트리거 하나만 있으면 됩니다.

pg공식 메뉴얼에는 파티션 구현 방법이 트리거와 rule이 있는데 대부분의 경우에 트리거방법을 사용하라고 하네요..

오라클은 파티션 그룹간 이동(그룹을 update했을때)이 자동으로 되지만 pg에서는 별도 작업이 필요합니다.

(5,6번작업)

--1. 메인 테이블 생성
 CREATE TABLE customers
(id integer primary key,
name varchar(126),
countrycode varchar(3),
contactnum text);

--2. 파티션 테이블 생성
CREATE TABLE customers_usa(PRIMARY KEY (id), CHECK (countrycode='USA')) INHERITS(customers);
CREATE TABLE customers_uk(PRIMARY KEY (id),CHECK (countrycode='UK')) INHERITS(customers);
CREATE TABLE customers_uae(PRIMARY KEY (id),CHECK (countrycode='UAE')) INHERITS(customers);
CREATE TABLE customers_ind(PRIMARY KEY (id),CHECK (countrycode='IND')) INHERITS(customers);
CREATE TABLE customers_ger(PRIMARY KEY (id),CHECK (countrycode='GER')) INHERITS(customers);
CREATE TABLE customers_rus(PRIMARY KEY (id),CHECK (countrycode='RUS')) INHERITS(customers);

--3. 함수 1
--메인테이블로 DML명령문 들어오면 파티션 테이블로 처리
--dynamic query사용해서 통합으로 이렇게도 할 수 있음.(이게 관리하기는 더 편할듯)
---INSERT/UPDATE/DELETE 나눠도 되기는 하나 이 방법이 단순함.

--2016.11.17, hm.ji, check사용하는 방법은 insert 트리거만 있으면 됨.
CREATE OR REPLACE FUNCTION public.customers_before_trig_func() RETURNS trigger AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
      EXECUTE 'INSERT INTO customers_'||NEW.countrycode||' VALUES ($1.*)' USING (NEW);
  END IF;

  RETURN NULL;
END
$$
LANGUAGE plpgsql;

--4.트리거 1
--트리거로 함수 연결
--INSERT/UPDAT/DELETE 각각 나눠도 되나 통합이 관리하기 편함.

--2016.11.17, hm.ji, check사용하는 방법은 insert 트리거만 있으면 됨.
CREATE TRIGGER customers_ins_trig before INSERT ON customers FOR each row
EXECUTE PROCEDURE customers_before_trig_func();

--5. 함수 2
-------
--파티션 모든 테이블에 적용해야 한다.
--update는 OLD Table에서 Delete 해주고 NEW Table로 Insert해줘야 한다.
-------
CREATE OR REPLACE FUNCTION customers_child_before_update_trig_func() RETURNS
trigger AS
$$
BEGIN

IF(OLD.countrycode <> new.countrycode) THEN
         delete from customers where id = OLD.id;
        INSERT INTO customers VALUES(NEW.*);
ELSE
    RAISE EXCEPTION 'Unknown country code we got';
END IF;

RETURN NULL;

END;
$$ LANGUAGE plpgsql;

--6.트리거 2

--추가 : 2016.11.17, hm.ji, pg 10버전에서는 오라클 처럼 파티션 그룹간에 이동을 자동으로 해준다고 합니다.
--★ 이 작업이 중요함. 오라클에서는 파티션 그룹간 데이터를 자동으로 이동해 주지만
--PG에서는 아래와 같이 메뉴얼로 작업해야함.
--파티션 테이블의 그룹간 이동을 위한 update
--이렇게 하지 않으면 파티션 한 그룹을 UPDATE 했을때 다른 파티션으로 옮겨지지 않는다.
--CHECK에서 설정한 에러가 나거나 UPDATE되지 않음.
--아래와 같은 에러 메시지
/*ERROR:  new row for relation "customers_rus" violates check constraint "customers_rus_countrycode_check"
DETAIL:  Failing row contains (1, D, USA, 6383383255).*/

CREATE TRIGGER customers_rus_upd_trig before UPDATE ON customers_uk FOR each row
EXECUTE PROCEDURE customers_child_before_update_trig_func();

CREATE TRIGGER customers_rus_upd_trig before UPDATE ON customers_ind FOR each row
EXECUTE PROCEDURE customers_child_before_update_trig_func();

CREATE TRIGGER customers_rus_upd_trig before UPDATE ON customers_ger FOR each row
EXECUTE PROCEDURE customers_child_before_update_trig_func();

CREATE TRIGGER customers_rus_upd_trig before UPDATE ON customers_uae FOR each row
EXECUTE PROCEDURE customers_child_before_update_trig_func();

CREATE TRIGGER customers_rus_upd_trig before UPDATE ON customers_rus FOR each row
EXECUTE PROCEDURE customers_child_before_update_trig_func();

CREATE TRIGGER customers_usa_upd_trig before UPDATE ON customers_usa FOR each row
EXECUTE PROCEDURE customers_child_before_update_trig_func();

--7. 샘플 데이터 입력
WITH stuff AS
(
SELECT
    array['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'] as names,
    array['USA', 'UK', 'UAE', 'IND', 'GER', 'RUS'] AS cnames
)
INSERT INTO customers
SELECT
    generate_series(1, 1000) id,
    names[(random()*100)::int%8+1] cname,
    cnames[(random()*100)::int%6+1] countrycode,
    ltrim(round(random()::numeric, 10)::text, '0.') contactnumber
FROM stuff;

--파티션 테이블 보기
SELECT
    inhrelid::regclass,
    inhparent::regclass, *
from pg_inherits
WHERE inhparent::regclass='customers'::regclass;

--8. 파티션 그룹간 이동 테스트

select * from customers where id = 1;
--GER에 소속됨.

--RUS로 update해서 파티션 이동
UPDATE customers
SET
    countrycode = 'RUS'
where id = 1;

--테이블 사이즈
select pg_relation_size('customers'); --customers 여기에는 데이터 저장안됨.
--SIZE 0
select pg_relation_size('customers_ger');
--SIZE 16384

--기타. 파티션 관리
--파티션 추가
create TABLE customers_jap(PRIMARY KEY (id), CHECK(countrycode='JAP')) INHERITS(customers);

--파티션 그룹간 이동을 위해 트리거로 함수 연결.
CREATE TRIGGER customers_usa_upd_trig before UPDATE ON customers_jap FOR each row
EXECUTE PROCEDURE customers_child_before_update_trig_func();

--파티션 해제
ALTER TABLE customers_jap NO INHERIT customers;

--다시 파티션 설정
ALTER TABLE customers_jap INHERIT customers;

참고 : Postgresql Development Essentials.