postgresql 按日期动态创建分区表

PostgreSQL中的动态分区表创建与触发器应用

原创已于 2023-06-08 16:41:50 修改 · 2.9k 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #postgresql

于 2023-06-08 14:27:07 首次发布

JAVA基础同时被 2 个专栏收录

33 篇文章

订阅专栏

数据库

2 篇文章

订阅专栏

该文描述了在PostgreSQL数据库中如何创建一个主表`tbl_vehicle_statistics`，并建立一个存储过程`auto_insert_tbl_vehicle_statistics`用于自动插入数据到按时间分区的子表。当在主表上执行INSERT操作时，触发器会检查并创建必要的分区表，同时为每个分区创建索引，确保数据正确分发。测试数据写入后，还提供了删除触发器和存储过程的命令。

1、创建主表

CREATE TABLE "public"."tbl_vehicle_statistics" (
  "id" serial PRIMARY KEY,
  "plate_code"  varchar(64),
  "plate_color" int4,
  "pass_time" timestamp
 );

2、创建存储过程

create or replace function auto_insert_tbl_vehicle_statistics()
returns trigger as 
$BODY$
declare 
    -- 分区后缀 
    column_name text := TG_ARGV[0];
    yyyymmddHHmmss text := to_char(NEW.pass_time,'yyyy-mm-dd hh24:mi:ss');  
    yyyymmdd text := to_char(NEW.pass_time,'yyyymmdd');  
    yyyymm text := to_char(NEW.pass_time,'yyyymm');  
    isExist boolean; 
    strSQL  text;
    startTime text;
    endTime text;
begin

	--判断对应分区表是否存在
	 select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||yyyymmdd);
	--若不存在分区表：
	if (isExist = false) then
	   --创建子分区表
	    startTime := yyyymm||'01 00:00:00';
	    endTime := date_trunc('month',NEW.pass_time)  + interval '1 month';
	    strSQL := 'create table if not exists '|| TG_RELNAME ||'_'||yyyymmdd||
	              ' ( CHECK('|| column_name ||'>='''|| startTime ||''' 
                    AND '|| column_name ||'< '''|| endTime ||''' )
                    ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;
       -- 创建索引
        strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||yyyymmdd||'_INDEX_'||column_name|| ' ON '
                 ||TG_RELNAME||'_'||yyyymmdd||' ('||column_name||');' ;
        EXECUTE strSQL;

	end if;
    -- 插入数据到子分区
       strSQL := 'INSERT INTO '||TG_RELNAME||'_'||yyyymmdd||' SELECT $1.* ;' ;
       EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
  
$BODY$
  language plpgsql;

关于postgreSQL触发器内置的一些变量及其说明：

内置变量名	说明
NEW	数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。
OLD	数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。
TG_NAME	数据类型是name，它保存实际被调用的触发器的名字。
TG_RELNAME	数据类型是name，表示触发器作用的表的名字。它与变量TG_TABLE_NAME的作用是一样的。
TG_NARGS	数据类型是integer，表示CREATE TRIGGER命令传给触发器过程的参数的个数。
TG_ARGV[]	数据类型是text类型的数组。表示CREATE TRIGGER命令传给触发器过程的所有参数。下标从0开始。TG_ARGV[0]表示第一个参数，TG_ARGV[1]表示第二个参数，以此类推。如果下标小于0或大于等于tg_nargs，将会返回一个空值。

3、为主表创建触发器

 CREATE TRIGGER tbl_vehicle_statistics_trigger
  BEFORE INSERT
  ON tbl_vehicle_statistics
  FOR EACH ROW
  EXECUTE PROCEDURE auto_insert_tbl_vehicle_statistics('pass_time');

4、测试数据写入

insert into tbl_vehicle_statistics(id,plate_code,plate_color,pass_time) 
VALUES(1,'京A0001',1,to_timestamp('2023-08-07 09:54:00','yyyy-mm-dd hh24:mi:ss'));

5、其他

（1）删除触发器

drop trigger tbl_vehicle_statistics_trigger on tbl_vehicle_statistics;

（2）删除存储过程

DROP function auto_insert_tbl_vehicle_statistics();