create table tbl_partition(

    id integer,

    name varchar(20),

    gender boolean,

    join_date date,

    dept char(4)

)

create table tbl_partition_201211(

    check( join_date>=DATE '2012-11-01' and join_date< DATE '2012-12-01' )

)inherits(tbl_partition);

create table tbl_partition_201212 (

check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )

) INHERITS (tbl_partition);

create table tbl_partition_201301 (

check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )

) INHERITS (tbl_partition);

create table tbl_partition_201302 (

check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' )

) INHERITS (tbl_partition);

create table tbl_partition_201303 (

check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' )

) INHERITS (tbl_partition);

create table tbl_partition_201304 (

check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' )

) INHERITS (tbl_partition);

create table tbl_partition_201305 (

check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' )

) INHERITS (tbl_partition);

create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date); 

create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);    

create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date); 

create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date); 

create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date); 

create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date); 

CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

    IF ( NEW.join_date >= DATE '2012-11-01' AND

         NEW.join_date < DATE '2012-12-01' ) THEN

        INSERT INTO tbl_partition_201211 VALUES (NEW.*);

    ELSIF ( NEW.join_date >= DATE '2012-12-01' AND

            NEW.join_date < DATE '2013-01-01' ) THEN

        INSERT INTO tbl_partition_201212 VALUES (NEW.*);

    ELSIF ( NEW.join_date >= DATE '2013-01-01' AND

            NEW.join_date < DATE '2013-02-01' ) THEN

        INSERT INTO tbl_partition_201301 VALUES (NEW.*);

    ELSIF ( NEW.join_date >= DATE '2013-02-01' AND

            NEW.join_date < DATE '2013-03-01' ) THEN

        INSERT INTO tbl_partition_201302 VALUES (NEW.*);

    ELSIF ( NEW.join_date >= DATE '2013-03-01' AND

            NEW.join_date < DATE '2013-04-01' ) THEN

        INSERT INTO tbl_partition_201303 VALUES (NEW.*);

    ELSIF ( NEW.join_date >= DATE '2013-04-01' AND

            NEW.join_date < DATE '2013-05-01' ) THEN

        INSERT INTO tbl_partition_201304 VALUES (NEW.*);

    ELSIF ( NEW.join_date >= DATE '2013-05-01' AND

            NEW.join_date < DATE '2013-06-01' ) THEN

        INSERT INTO tbl_partition_201305 VALUES (NEW.*);

    ELSE

        RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';

    END IF;

    RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER insert_tbl_partition_traigger

    BEFORE INSERT ON tbl_partition

    For EACH ROW

    EXECUTE PROCEDURE tbl_partition_insert_trigger();

insert into tbl_partition values (1, 'David', '', '2013-01-10', 'TS');

insert into tbl_partition values (2, 'Sandy', '', '2013-02-10', 'TS');

insert into tbl_partition values (3, 'Eagle', '', '2012-11-01', 'TS');

insert into tbl_partition values (4, 'Miles', '', '2012-12-15', 'SD');

insert into tbl_partition values (5, 'Simon', '', '2012-12-10', 'SD');

insert into tbl_partition values (6, 'Rock', '', '2012-11-10', 'SD');

insert into tbl_partition values (7, 'Peter', '', '2013-01-11', 'SD');

insert into tbl_partition values (8, 'Sally', '', '2013-03-10', 'BCSC');

insert into tbl_partition values (9, 'Carrie', '', '2013-04-02', 'BCSC');

insert into tbl_partition values (10, 'Lee', '', '2013-01-05', 'BMC');

insert into tbl_partition values (11, 'Nicole', '', '2012-11-10', 'PROJ');

insert into tbl_partition values (12, 'Renee', '', '2013-01-10', 'TS');

alter table tbl_partition_201304 no inherit tbl_partition;