Postgresql 添加分区表(按月和按日通用)

建了分区表的同学相信添加分区表很头疼，如果有按月分区又有按日分区的，而且是通过"_yyyymmdd"或者"_yyyymm"后缀进行分区的，那么可以用这个函数进行添加分区

 

CREATE OR REPLACE FUNCTION f_add_partition()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
v_max_childname text;  --最大子表名称
v_parentname text;  --子表对应的父表名称
v_suffix text;  --子表日期后缀
sql text; --要执行的SQL语句
GRA_TO text;   --赋予权限的语句
begin
--取出分区表最大分区表以及父表名称
for v_max_childname, v_parentname in select max(inhrelid::regclass::text),inhparent::regclass from pg_inherits where  inhparent::regclass::text not like '%.%' group by inhparent
    loop
    raise notice '最大子表:%,父表:%',v_max_childname,v_parentname;  
    sql= 'select split_part('''|| v_max_childname  || ''',''_'',(length(''' || v_max_childname || ''')-length(replace(''' || v_max_childname || ''',''_'',''''))+1))';   --取出日期是按月还是按日
    execute sql into v_suffix;      --将取出的日期存入v_suffix 
    while(length(v_suffix)=6 and v_suffix<'202512')  --判断如果是按月，那么循环执行建表语句并且赋予权限
        loop
        v_suffix=to_char (to_timestamp(v_suffix,'yyyymm')+interval '1 month','yyyymm') ; --在取出的分区表日期按月+1
        sql= 'create table '||v_parentname ||'_'||v_suffix || '(like ' || v_parentname ||' including all)  inherits ('|| v_parentname ||')';
        execute sql; 
    for GRA_TO in execute 'select ''grant ''||privilege_type||'' on '||v_parentname||'_'||v_suffix ||' to ''||grantee from information_schema.table_privileges where table_name='''||v_max_childname||'''' loop
        execute GRA_TO;
        end loop;
        end loop;
    while(length(v_suffix)=8 and v_suffix<'20251231')  --判断如果是按日分区，循环执行后面的建表语句并且赋予权限
        loop
        v_suffix=to_char (to_timestamp(v_suffix,'yyyymmdd')+interval '1 day','yyyymmdd') ;
        sql= 'create table '||v_parentname||'_'||v_suffix || '(like ' || v_parentname ||' including all)  inherits ('|| v_parentname ||')';
        execute sql;
    for GRA_TO in execute 'select ''grant ''||privilege_type||'' on '||v_parentname||'_'||v_suffix||' to ''||grantee from information_schema.table_privileges where table_name='''||v_max_childname||'''' loop
        execute GRA_TO;
        end loop;
        end loop;
    end loop;
    RAISE NOTICE 'Partition table has be created successfully！';
end;
$function$;

 

 

\dt 

 hank   | tbl              | table | hank

 hank   | tbl_20140322     | table | hank

 hank   | test             | table | hank

 hank   | test_201405      | table | hank

select f_add_partition();

\dt

 

 hank   | tbl              | table | hank

 hank   | tbl_20140322     | table | hank

 按日分区的显示太多，此处省略一万行。。。。。

 hank   | tbl_20151230     | table | hank

 hank   | tbl_20151231     | table | hank

 

按月的比较少，就全部贴出来了

 hank   | test             | table | hank

 hank   | test_201405      | table | hank

 hank   | test_201406      | table | hank

 hank   | test_201407      | table | hank

 hank   | test_201408      | table | hank

 hank   | test_201409      | table | hank

 hank   | test_201410      | table | hank

 hank   | test_201411      | table | hank

 hank   | test_201412      | table | hank

 hank   | test_201501      | table | hank

 hank   | test_201502      | table | hank

 hank   | test_201503      | table | hank

 hank   | test_201504      | table | hank

 hank   | test_201505      | table | hank

 hank   | test_201506      | table | hank

 hank   | test_201507      | table | hank

 hank   | test_201508      | table | hank

 hank   | test_201509      | table | hank

 hank   | test_201510      | table | hank

 hank   | test_201511      | table | hank

 hank   | test_201512      | table | hank