PG建立分区

# 建父表
create table sales_detail (
	product_id int not null,
	price numeric(12,2),
	amount int not null,
	sale_date date not null,
	buyer varchar(40),
	buyer_contarct text
);
# 建子表
create table sales_detail_y2014m01(check (sale_date >= date '2014-01-01' and sale_date < date '2014-02-01')) inherits (sales_detail);
create table sales_detail_y2014m02(check (sale_date >= date '2014-02-01' and sale_date < date '2014-03-01')) inherits (sales_detail);
create table sales_detail_y2014m03(check (sale_date >= date '2014-03-01' and sale_date < date '2014-04-01')) inherits (sales_detail);
create table sales_detail_y2014m04(check (sale_date >= date '2014-04-01' and sale_date < date '2014-05-01')) inherits (sales_detail);
create table sales_detail_y2014m05(check (sale_date >= date '2014-05-01' and sale_date < date '2014-06-01')) inherits (sales_detail);
create table sales_detail_y2014m06(check (sale_date >= date '2014-06-01' and sale_date < date '2014-07-01')) inherits (sales_detail);
create table sales_detail_y2014m07(check (sale_date >= date '2014-07-01' and sale_date < date '2014-08-01')) inherits (sales_detail);
create table sales_detail_y2014m08(check (sale_date >= date '2014-08-01' and sale_date < date '2014-09-01')) inherits (sales_detail);
create table sales_detail_y2014m09(check (sale_date >= date '2014-09-01' and sale_date < date '2014-10-01')) inherits (sales_detail);
create table sales_detail_y2014m10(check (sale_date >= date '2014-10-01' and sale_date < date '2014-11-01')) inherits (sales_detail);
create table sales_detail_y2014m11(check (sale_date >= date '2014-11-01' and sale_date < date '2014-12-01')) inherits (sales_detail);
create table sales_detail_y2014m12(check (sale_date >= date '2014-12-01' and sale_date < date '2015-01-01')) inherits (sales_detail);
# 字表索引
create index sale_detail_y2014m04_sale_date on sales_detail_y2014m01 (sale_date);
create index sale_detail_y2014m02_sale_date on sales_detail_y2014m02 (sale_date);
create index sale_detail_y2014m03_sale_date on sales_detail_y2014m03 (sale_date);
create index sale_detail_y2014m04_sale_date on sales_detail_y2014m04 (sale_date);
create index sale_detail_y2014m05_sale_date on sales_detail_y2014m05 (sale_date);
create index sale_detail_y2014m06_sale_date on sales_detail_y2014m06 (sale_date);
create index sale_detail_y2014m07_sale_date on sales_detail_y2014m07 (sale_date);
create index sale_detail_y2014m08_sale_date on sales_detail_y2014m08 (sale_date);
create index sale_detail_y2014m09_sale_date on sales_detail_y2014m09 (sale_date);
create index sale_detail_y2014m10_sale_date on sales_detail_y2014m10 (sale_date);
create index sale_detail_y2014m12_sale_date on sales_detail_y2014m12 (sale_date);
# 建触发器
CREATE OR REPLACE FUNCTION sale_detail_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.sale_date >= DATE '2014-01-01' AND
NEW. sale_date < DATE '2014-02-01' ) THEN
INSERT INTO sales_detail_y2014m01 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-02-01' AND
NEW.sale_date < DATE '2014-03-01' ) THEN
INSERT INTO sales_detail_y2014m02 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-03-01' AND
NEW.sale_date < DATE '2014-04-01' ) THEN
INSERT INTO sales_detail_y2014m03 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-04-01' AND
NEW.sale_date < DATE '2014-05-01' ) THEN
INSERT INTO sales_detail_y2014m04 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-05-01' AND
NEW.sale_date < DATE '2014-06-01' ) THEN
INSERT INTO sales_detail_y2014m05 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-06-01' AND
NEW.sale_date < DATE '2014-07-01' ) THEN
INSERT INTO sales_detail_y2014m06 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-07-01' AND
NEW.sale_date < DATE '2014-08-01' ) THEN
INSERT INTO sales_detail_y2014m07 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-08-01' AND
NEW.sale_date < DATE '2014-09-01' ) THEN
INSERT INTO sales_detail_y2014m08 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-09-01' AND
NEW.sale_date < DATE '2014-10-01' ) THEN
INSERT INTO sales_detail_y2014m09 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-10-01' AND
NEW.sale_date < DATE '2014-11-01' ) THEN
INSERT INTO sales_detail_y2014m10 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-11-01' AND
NEW.sale_date < DATE '2014-12-01' ) THEN
INSERT INTO sales_detail_y2014m11 VALUES (NEW.*);

ELSIF ( NEW.sale_date >= DATE '2014-12-01' AND
NEW.sale_date < DATE '2015-01-01' ) THEN
INSERT INTO sales_detail_y2014m12 VALUES (NEW.*);

ELSE
RAISE EXCEPTION 'Date out of range. Fix the
sale_detail_insert_trigger () function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_sale_detail_trigger
BEFORE INSERT ON sales_detail
FOR EACH ROW EXECUTE PROCEDURE
sale_detail_insert_trigger ();

# 数据插入：
insert into sales_detail values(1,43.12,1,date '2014-01-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-02-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-03-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-04-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-05-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-06-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-07-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-08-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-09-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-10-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-11-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-12-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-01-02', '李四', '四川yy');

# 数据测试
select * from sales_detail limit 10;

select * from sales_detail_y2014m01 limit 1;

规则实现触发器功能

CREATE RULE sales_detail_insert_y2014m01 AS

ON INSERT TO sales_detail WHERE

( sale_date >= DATE '2014-01-01' AND sale_date < DATE
'2014-02-01' )
DO INSTEAD
INSERT INTO sales_detail_y2014m01 VALUES (NEW.);
CREATE RULE sales_detail_insert_y2014m02 AS
ON INSERT TO sales_detail WHERE
( sale_date >= DATE '2014-02-01' AND sale_date < DATE
'2014-03-01' )
DO INSTEAD
INSERT INTO sales_detail_y2014m01 VALUES (NEW.);
….
CREATE RULE sales_detail_insert_y2014m12 AS
ON INSERT TO sales_detail WHERE
( sale_date >= DATE '2014-12-01' AND sale_date < DATE

'2015-01-01' )

DO INSTEAD

INSERT INTO sales_detail_y2014m12 VALUES (NEW.*);