postgresql按日期自动创建分区表

按月自动创建分区表

创建一个函数，该函数包含分区的判断逻辑

CREATE OR REPLACE FUNCTION public.auto_insert_before_table()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    time_column_name     text ;            -- 父表中用于分区的时间字段的名称[必须首先初始化!!]
    curMM         varchar(6);        -- 'YYYYMM'字串,用做分区子表的后缀
    isExist         boolean;        -- 分区子表,是否已存在
    startTime         text;
    endTime        text;
    strSQL          text;
    partitionTableName   text; -- 分区表的完整名称
    
BEGIN
    -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
    time_column_name := TG_ARGV[0];
   
    -- 判断对应分区表 是否已经存在?
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curMM := to_char( strSQL::timestamp , 'YYYYMM' );

 -- 构建分区表的名称  
    partitionTableName := TG_RELNAME || '_' || curMM; 
    
        -- 检查分区表是否已存在  
    SELECT EXISTS(SELECT 1 FROM pg_class WHERE relname = partitionTableName)  
        INTO isExist;  
    
    -- 若不存在, 则插入前需 先创建子分区
    IF  not isExist  THEN  
        -- 创建子分区表
        startTime := curMM||'01 00:00:00.000';
        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;
    
    -- 设置主键索引，
        strSQL := format('ALTER TABLE %I ADD PRIMARY KEY (%I);',   
                 partitionTableName,   
                 'id');  
       EXECUTE strSQL;
       
    END IF;
 
    -- 插入数据到子分区! ON CONFLICT (id) DO NOTHING  当遇到id重复时，不插入
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.* ON CONFLICT (id) DO NOTHING' ;
    EXECUTE strSQL USING NEW;
    
    RETURN null;  -- new 返回受影响的行数;此处返回NULL, 触发这个触发器的INSERT/UPDATE/DELETE命令不会被执行。如果行级的BEFORE触发器返回非空的值，则INSERT/UPDATE/DELETE命令继续执行
END
$function$

创建一个触发器，绑定上述函数

-- time是传参用的，用于函数里面的time_column_name
CREATE TRIGGER insert_table_trigger
  BEFORE INSERT
  ON `table`
  FOR EACH ROW
  EXECUTE PROCEDURE auto_insert_before_table('time');

执行insert的语句就会调用触发器，先进行判断是否有time对应的分区表，然后在函数内执行insert语句，如果需要在插入后执行业务代码，则修改RETURN null; 改为RETURN new;

---

按日自动创建分区表

创建一个函数，该函数包含分区的判断逻辑，此处需要注意curMM的长度，已经变成8位

CREATE OR REPLACE FUNCTION public.auto_insert_demo_tab()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    time_column_name     text ;            -- 父表中用于分区的时间字段的名称[必须首先初始化!!]
    curMM         varchar(8);        -- 'YYYYMMdd'字串,用做分区子表的后缀,该长度有8位。
    isExist         boolean;        -- 分区子表,是否已存在
    startTime         text;
    endTime        text;
    strSQL          text;
     partitionTableName   text; -- 分区表的完整名称
    
BEGIN
    -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
    time_column_name := TG_ARGV[0];
    
   
    -- 判断对应分区表 是否已经存在?
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curMM := to_char( strSQL::timestamp , 'YYYYMMDD' );
    
    
     -- 构建分区表的名称  
    partitionTableName := TG_RELNAME || '_' || curMM;  
    
    select count(*) INTO isExist from pg_class where relname = (partitionTableName);
 

    -- 若不存在, 则插入前需 先创建子分区
    IF ( isExist = false ) THEN  
        -- 创建子分区表
        startTime := curMM;
        endTime := (strSQL::date) + INTERVAL '1 day';
        
        
        strSQL := 'CREATE TABLE IF NOT EXISTS '||partitionTableName||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;
    
     -- 创建索引
        strSQL := 'CREATE INDEX '||partitionTableName||'_INDEX_'||time_column_name||' ON '
                  ||partitionTableName||' ('||time_column_name||');' ;
        EXECUTE strSQL;
    
        strSQL := format('ALTER TABLE %I ADD PRIMARY KEY (%I);',   
                 partitionTableName,   
                 'id');  
       EXECUTE strSQL;
       
    END IF;
 
    -- 插入数据到子分区!
    strSQL := 'INSERT INTO '||partitionTableName||' SELECT $1.* ON CONFLICT (id) DO NOTHING' ;
    EXECUTE strSQL USING NEW;
    RETURN null; 
END
$function$

创建一个触发器，代码同上