#title PostgreSQL Partitioning

data type이 맞지 않으면, trigger 타기도 전에 뻑난다.

{{{
drop table test_child1;
drop table test_child2;
drop table test_parent;

--부모 테이블
create table test_parent(
	id int
,	logs json
,	src text
);

--자식테이블: 파티션 추가
create table test_child1(check(id = 1)) inherits (test_parent);
create table test_child2(check(id = 2)) inherits (test_parent);

--트리거 함수
create or replace function test_insert_trigger()
returns trigger as
$$
declare logs json;
declare	ins_sql text;
begin
	logs := new.src;
	ins_sql='insert into test_child' ||new.id||'(id, logs) values('||new.id||', '''||new.src||''')';
	execute ins_sql;
	return null;
exception
	when others then
		ins_sql='insert into test_child' ||new.id||'(id, src) values('||new.id||', '''||new.src||''')';
		execute ins_sql;
		return null;
end;
$$ language plpgsql;

--트리거
create trigger insert_test_trigger
    before insert on test_parent
    for each row execute procedure test_insert_trigger();

--테스트
insert into test_parent(id, src) values(1, '{"kr":"대한민국", "en":"KOREA"}');
insert into test_parent(id, src) values(1, '{"kr":"대한민국", "en":"KOREA"');

select * from test_parent;


--파티션 잘 타나?
select * from test_parent
where id = 1;

/*
"Append  (cost=0.00..20.38 rows=5 width=68)"
"  ->  Seq Scan on test_parent  (cost=0.00..0.00 rows=1 width=68)"
"        Filter: (id = 1)"
"  ->  Seq Scan on test_child1  (cost=0.00..20.38 rows=4 width=68)"
"        Filter: (id = 1)"
*/

--파티션 삭제
alter table test_child1 no inherit test_parent;

select * from test_parent
where id = 1;
--0 rows

--drop table test_child1
}}}