之前写了一篇按月分表的文章,由于数据量还是比较多,再细分为按周分表。原理是一样的,只不过修改一下分表的规则,因此修改过的触发器类似下面:
1、建立主表
drop table if exists tbl_week;
CREATE TABLE tbl_week
(
date_key date,
hour_key smallint
);
2、定义一个函数
输入一个日期时间获取该时间在那一周的周一的日期,比如2018-11-17得到的就是2018-11-12
CREATE OR REPLACE FUNCTION get_week_first_day(in in_date date,out out_date text)
AS $$
DECLARE mon_date date;
BEGIN
select in_date - (extract (dow from in_date) - 1 || ' day')::interval INTO mon_date;
SELECT to_char(mon_date, 'YYYY_MM_DD') INTO out_date;
END;
$$
LANGUAGE plpgsql;
3、自动建表触发器
CREATE OR REPLACE FUNCTION tbl_week_trigger()
RETURNS TRIGGER AS $$
DECLARE this_week_first_day_text TEXT;
next_week_first_day_text TEXT;
insert_statement TEXT;
BEGIN
SELECT get_week_first_day(NEW.date_key) INTO this_week_first_day_text;
SELECT to_char(to_date(this_week_first_day_text,'YYYY-MM-DD') + interval '7 day', 'YYYY-MM-DD') INTO next_week_first_day_text;
insert_statement := 'INSERT INTO tbl_week_'
|| this_week_first_day_text||' VALUES ($1.*)';
EXECUTE insert_statement USING NEW;
RETURN NULL;
EXCEPTION
WHEN UNDEFINED_TABLE
THEN
EXECUTE
'CREATE TABLE IF NOT EXISTS tbl_week_'
|| this_week_first_day_text
|| '(CHECK (date_key >= '''
|| this_week_first_day_text
|| ''' and date_key<'''
|| next_week_first_day_text
|| ''')) INHERITS (tbl_week)';
RAISE NOTICE 'CREATE NON-EXISTANT TABLE tbl_week_%', this_week_first_day_text;
EXECUTE
'CREATE INDEX tbl_week_date_key_'
|| this_week_first_day_text
|| ' ON tbl_week_'
|| this_week_first_day_text
|| '(date_key)';
EXECUTE insert_statement USING NEW;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
4、挂载分区Trigger
CREATE TRIGGER insert_tbl_week_trigger
BEFORE INSERT ON tbl_week
FOR EACH ROW EXECUTE PROCEDURE tbl_week_trigger();
5、分表效果
插入数据后生成了上述一些表,每个表的后缀都是周一的日期。