----创建分区策略管理表。

create table partition_manage_policies(

schemaname varchar,

tablename varchar,

lifecycle int

)distribute by replication ;

----查看当前数据库中的分区表

select n.nspname as schemaname,c.relname as tablename from pg_class c

left join pg_namespace n on n.oid = c.relnamespace

where c.parttype = 'p';

----给某张指定分区表插入分区策略，例如public. day_manager_test保留30天的数据。

insert into partition_manage_policies values('public','day_manager_test',30);

insert into partition_manage_policies values('public','hour_manager_test',30);

select * from partition_manage_policies;

----创建测试分区表

----1）按日分区测试表

CREATE TABLE public.day_manager_test(

  guid varchar ,

  user_id varchar ,

  name varchar ,

  dt timestamp)distribute by hash(guid)

  partition by range(dt)

 (partition p1 start(timestamp '2021-07-10') end (timestamp '2021-08-11') every(interval '1 days'));

----2）按小时分区测试表

CREATE TABLE public.hour_manager_test(

 guid varchar,

 user_id varchar,

 name varchar,

dt timestamp) with(orientation = column) distribute by hash(guid)

 partition by range(dt)

 (partition p1 start(timestamp '2021-07-10 01:00:00') end (timestamp '2021-08-11 00:00:00') every(interval '1 hours') );

--查看指定表(public. day_manager_test)的分区信息，以及分区的上边界。

select n.nspname as schemaname,c.relname as tablename,

p.relname as partname,p.boundaries::varchar as boundaries

from PG_PARTITION p

left join pg_class c on p.parentid = c.oid

left join pg_namespace n on c.relnamespace = n.oid 

where c.relname = 'day_manager_test'

and n.nspname = 'public'

and p.relname <> 'day_manager_test'

order by p.boundaries desc;

---按日分区管理函数，根据gtmdate新增当前日期及之前的分区。例如gtmdate是'2021-08-17'，会新建分区保存当天数据，并且会补充gtmdate前缺省的分区。

CREATE OR REPLACE FUNCTION partition_day_manage(schema_name varchar, table_name varchar, gmtdate varchar) RETURNS void AS

$$

DECLARE

r partition_manage_policies%rowtype;

lifecycle int;

sql_str varchar;

drop_date varchar;

drop_partition_name varchar;

add_partition_name varchar;

partition_max_num int;

partition_max_value timestamp; 

partition_perfix varchar;

loop_num int;

err_message varchar;

BEGIN

SELECT * into r FROM public.partition_manage_policies where schemaname = schema_name and tablename = table_name;        

drop_date := gmtdate::date - r.lifecycle + 1;

--按照lifecycle信息计算删除日期并打印

dbms_output.put ('------ Delete partition ');

dbms_output.put(drop_date);

dbms_output.put_line(' and earlier! ------');

dbms_output.put_line('------ Prints the delete partition command! ------');

--打印删除分区的命令

--删除所有小于或等于drop_date的日期的分区

for drop_partition_name in select p.relname from PG_PARTITION p left join pg_class c on p.parentid = c.oid left join pg_namespace n on c.relnamespace = n.oid  where c.relname = table_name and n.nspname = schema_name and p.relname <> table_name and substr(p.boundaries::varchar,instr(p.boundaries::varchar,'20', 1),10) <= drop_date loop

    sql_str := 'alter table '||schema_name||'.'||table_name||' drop partition '||drop_partition_name;

    dbms_output.put_line(sql_str); --打印删除分区sql

         EXECUTE IMMEDIATE sql_str; --执行删除分区

end loop;

--取当前分区的最大value值、分区名的最大值、分区名称的前缀,其中instr中的'20'代表'2020'中的20。

select

substr(p.boundaries::varchar,instr(p.boundaries::varchar,'20', 1),10),substr(p.relname,instr(p.relname,'_',1)+1),substr(p.relname,0,instr(p.relname,'_',1)) into partition_max_value,partition_max_num,partition_perfix from PG_PARTITION p left join pg_class c on p.parentid = c.oid left join pg_namespace n on c.relnamespace = n.oid 

where c.relname = table_name

and n.nspname = schema_name

and p.relname <> table_name

order by p.boundaries desc limit 1;

--计算需要生成多少个分区，当输入的日期小于最大分区的日期时，该值为负数，则如下for循环不会执行

loop_num = substr((gmtdate::timestamp - partition_max_value::timestamp)::varchar,0,instr((gmtdate::timestamp - partition_max_value::timestamp)::varchar,' ',1)) + 1;

--自当前最大分区值开始添加分区

--打印添加分区的sql

dbms_output.put_line('------ Prints the add partition command! ------');

for i in 1..loop_num loop

    partition_max_num = partition_max_num + 1; --分区名为自增序列

    add_partition_name = partition_perfix||partition_max_num; --拼接分区名称

    partition_max_value = partition_max_value + interval '1 day'; --计算分区值，每个分区间隔1天

    sql_str := 'alter table '||schema_name||'.'||table_name||' add partition '||add_partition_name||' values less than (''' ||partition_max_value||''')';

         dbms_output.put_line(sql_str);

         EXECUTE IMMEDIATE sql_str; --执行添加分区sql

end loop;

    RETURN;

--如果分区表策略管理表中没有表信息，报错提示

   EXCEPTION when NO_DATA_FOUND THEN

    err_message := 'table '||schema_name||'.'||table_name || ' not found in public.partition_manage_policies.';

    RAISE EXCEPTION '%', err_message; 

END $$ LANGUAGE 'plpgsql' ;

call partition_day_manage('public','day_manager_test',current_date);

提供日志打印如下信息：

（1）       删除了’2021-07-19’及之前的分区。

（2）       执行的删除分区表语句。

（3）       执行的添加分区表语句。

--查看更新后的分区信息，以及分区的上边界。

call partition_day_manage('public','day_manager_test','2021-08-13');

--不执行任何删除和添加分区命令

call partition_day_manage('public','day_manager_test_notexist','2021-08-29');

--报错，分区策略管理表中不存在该表