psql按月分表之触发器自动建表

1、创建主表
通过主表定义好表的结构，需要一个分表的关键字,比如下面的date_key,分表的时候会根据这个字段所在的表的范围决定插入到哪张分区表中。

drop table if exists tbl_partition;
CREATE TABLE tbl_partition
(
  date_key date,
  hour_key smallint
);

2、 函数用来计算某一天所在月的1号

CREATE OR REPLACE FUNCTION get_month_first_day(in in_date date, out out_date text)
AS $$
BEGIN
  SELECT to_char(in_date, 'YYYY_MM') || '_01' INTO out_date;
END;
$$
LANGUAGE plpgsql;

3、定义触发器
自动建表的触发器实现，当插入一条数据时根据date_key字段的值判断是否需要建表

CREATE OR REPLACE FUNCTION tbl_partition_trigger()
  RETURNS TRIGGER AS $$
DECLARE month_text TEXT;
        this_month_first_day_text TEXT;
        next_month_first_day_text TEXT;
        insert_statement TEXT;
        --real_table_name TEXT NOT NULL := 'tbl_partition';
        --check_col_name TEXT NOT NULL := 'date_key';
BEGIN
  SELECT to_char(NEW.date_key, 'YYYY_MM') INTO month_text;
  SELECT get_month_first_day(NEW.date_key) INTO this_month_first_day_text;
  SELECT to_char(to_date(this_month_first_day_text,'YYYY-MM-DD') + interval '1 month', 'YYYY-MM-DD') INTO next_month_first_day_text;
  insert_statement := 'INSERT INTO tbl_partition_' || month_text || ' VALUES ($1.*)';
  EXECUTE insert_statement USING NEW;
  RETURN NULL;
  EXCEPTION
  WHEN UNDEFINED_TABLE
    THEN
      --建表
      EXECUTE
      'CREATE TABLE IF NOT EXISTS tbl_partition_'
      || month_text
      || '(CHECK (date_key >= '''
      || this_month_first_day_text
      || ''' and date_key < '''
      || next_month_first_day_text
      || ''')) INHERITS (tbl_partition)';
      RAISE NOTICE 'CREATE NON-EXISTANT TABLE tbl_partition_%', month_text;
      --创建索引
      EXECUTE
      'CREATE INDEX tbl_partition_date_key_'
      || month_text
      || ' ON tbl_partition_'
      || month_text
      || '(date_key)';
      --创建主键
      EXECUTE
      'ALTER TABLE tbl_partition_' 
      || month_text 
      || ' ADD CONSTRAINT tbl_partition_' 
      || month_text 
      || '_pkey PRIMARY KEY (date_key, hour_key)';
      EXECUTE insert_statement USING NEW;
      RETURN NULL;
END;
$$
LANGUAGE plpgsql;

4、挂载分区Trigger
即将定义的触发器和主表进行关联起来

CREATE TRIGGER insert_tbl_partition_trigger
BEFORE INSERT ON tbl_partition
FOR EACH ROW EXECUTE PROCEDURE tbl_partition_trigger();

5、设置constraint_exclusion
    constraint_exclusion 的含义是：当PG生产执行计划时是否考虑表上的约束，这个参数有三个选项 “off,on,partition”。默认参数为 off, 意思不使用表上的 constraint 来生成计划；如果设置成 on，则对所有表上的 constraint生效；建议设置成partition，只对分区表生效，从而避免扫描所有分区。

set constraint_exclusion = partition;

6、插入数据脚本的示例

INSERT INTO tbl_partition
values('2018-12-01','12'),('2017-12-03','13'),('2018-11-03','2'),('2017-12-02','3');

7、部分截图效果

之后随便插入一条数据会根据数据的date_key决定插入到哪个月分区表中区，在java代码中直接可以不用关心如何去分区或者插入，和访问普通的数据库表操作没有区别。