Postgresql daily auto partition

-- 부모 테이블 생성

CREATE TABLE MEMBER ( 

    ID CHARACTER VARYING NOT NULL,

    EMAIL CHARACTER VARYING NOT NULL,

    REGISTER DATE DEFAULT CURRENT_TIMESTAMP NOT NULL

) WITH ( OIDS=FALSE);

-- 부모테이블을 상속받아 REGISTER 를 체크하여 자식 테이블을 자동으로 생성해주도록 한다.

CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS

  $BODY$

    DECLARE

      partition_date TEXT;

      partition TEXT;

    BEGIN

      partition_date := to_char(NEW. REGISTER,'YYYY_MM_DD');

      partition := TG_RELNAME || '_' || partition_date;

      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN

        RAISE NOTICE 'A partition has been created %',partition;

        EXECUTE 'CREATE TABLE ' || partition || ' (check (REGISTER = ''' || NEW. REGISTER || ''')) INHERITS (' || TG_RELNAME || ');';

      END IF;

      EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').*;';

      RETURN NULL;

    END;

  $BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

-- 트리거 등록

CREATE TRIGGER MEMBER_PARTITION_INSERT_TRIGGER

BEFORE INSERT ON MEMBER

FOR EACH ROW EXECUTE PROCEDURE CREATE_PARTITION_AND_INSERT();

-- 테스트 데이터 입력

INSERT INTO MEMBER VALUES ('aaa','aaa@aaa.com');

테스트 결과:

     입력 날짜에 따라 테이블 생성 및 데이터가 입력된다.

참고: 

http://blog.l1x.me/post/2016/02/16/creating-partitions-automatically-in-postgresql.html