PostgreSQL partition table name convert to data type

背景

关于PostgreSQL的分区表监控, 如下 :

http://blog.163.com/digoal@126/blog/static/16387704020130433036377/

将表名转成日期的方法举例 :

方法1 :

用到规则表达式的替换和转数组函数.

select   
regexp_split_to_array(  
  regexp_replace(  
    rtrim(  
      ltrim(  
        regexp_replace('b2.ffw2wf2ab2012_11_31_fe_2'  
 ,'[^[:digit:]]','_','g')  
      ,'_')  
    ,'_')  
  ,'_+','_','g')  
,'_');  

结果 :

inherit_mon=> select   
inherit_mon-> regexp_split_to_array(  
inherit_mon(>   regexp_replace(  
inherit_mon(>     rtrim(  
inherit_mon(>       ltrim(  
inherit_mon(>         regexp_replace('b2.ffw2wf2ab2012_11_31_fe_2'  
inherit_mon(> ,'[^[:digit:]]','_','g')  
inherit_mon(>       ,'_')  
inherit_mon(>     ,'_')  
inherit_mon(>   ,'_+','_','g')  
inherit_mon(> ,'_');  
 regexp_split_to_array   
-----------------------  
 {2,2,2,2012,11,31,2}  
(1 row)  

表名转日期的函数 :

create or replace function mon_part_conv_to_date(i_relname text) returns date as $$  
declare  
  arr text[] := ARRAY[0];  
  v_date text := '';  
  i text := '';  
  v_len int := 0;  
begin  
  arr := regexp_split_to_array( regexp_replace( rtrim( ltrim( regexp_replace(i_relname,'[^[:digit:]]','_','g') ,'_') ,'_') ,'_+','_','g') ,'_');  
  FOREACH i IN ARRAY arr  
  loop  
    if (length(v_date) = 8) then  
      exit;  
    end if;  
    if (length(i) = 4 and length(v_date)=0 and (substr(i,1,2) >= '18' and substr(i,1,2) <= '22')) then  
      v_date := v_date||i;  
    elsif (length(i) = 6 and length(v_date)=0 and (substr(i,1,2) >= '18' and substr(i,1,2) <= '22') and (substr(i,5,2)>='01' and substr(i,5,2)<='12')) then  
      v_date := v_date||i;  
    elsif (length(i) = 8 and length(v_date)=0 and (substr(i,1,2) >= '18' and substr(i,1,2) <= '22') and (substr(i,5,2)>='01' and substr(i,5,2)<='12') and (substr(i,7,2)>='01' and substr(i,7,2)<='31')) then  
      v_date := v_date||i;  
    elsif (length(i)=4 and length(v_date) = 4 and (substr(i,1,2)>='01' and substr(i,1,2)<='12') and (substr(i,3,2)>='01' and substr(i,3,2)<='31')) then  
      v_date := v_date||i;  
    elsif (length(i)=2 and length(v_date) = 4 and (substr(i,1,2)>='01' and substr(i,1,2)<='12')) then  
      v_date := v_date||i;  
    elsif (length(i)=2 and length(v_date) = 6 and (substr(i,1,2)>='01' and substr(i,1,2)<='31')) then  
      v_date := v_date||i;  
    else  
      continue;  
    end if;  
  end loop;  
  v_len := length(v_date);  
  case v_len  
  when 4 then -- 按年分表  
    return (v_date||'0101')::date;  
  when 6 then -- 按月分表  
    return (v_date||'01')::date;  
  when 8 then -- 按天分表  
    return v_date::date;  
  else -- 长度为5和7非日期分表  
    return '22220101'::date;  
  end case;  
exception when others then  
  return '22220101'::date;  
end;  
$$ language plpgsql;  

测试几种表的格式正确转换 :

inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012_12_01');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-01  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012_12_02');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-02  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012_12_1');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-01  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012_12_2');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-01  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012_1203');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-03  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_20121203');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-03  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012');  
 mon_part_conv_to_date   
-----------------------  
 2012-01-01  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_201212');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-01  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_20121202');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-02  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012s12f0001');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-01  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal1234.tbl_2012s12f0002');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-01  
(1 row)  
inherit_mon=> select mon_part_conv_to_date('digoal20341221.tbl_2012s12f0002');  
 mon_part_conv_to_date   
-----------------------  
 2034-12-21  
(1 row)  
  
inherit_mon=> select mon_part_conv_to_date('digoal20341241.tbl_2012s12f0002');  
 mon_part_conv_to_date   
-----------------------  
 2012-12-01  
(1 row)  

适用于含 .yyyy.[mm].*[dd] 格式的表名转换.

其中mm和dd可选.

也就是说适合按年分表, 按月分表, 按日分表的表名转换.

方法2, 使用父表表名作为prefix, 将子表名的剩余部分的数字全部截取出来 :

create or replace function mon_part_conv_to_date(i_relname text, i_prefix text) returns date as $$  
declare  
  v_len int;  
  v_date text := '';  
  i text := '';  
  v_suffix text := '';  
begin  
  v_suffix := replace(i_relname,i_prefix,'');  
  FOREACH i IN ARRAY regexp_split_to_array(v_suffix, '')  
  loop  
    if (i >= '0' and i <= '9') then  
      v_date := v_date||i;  
    end if;  
  end loop;  
  v_len := length(v_date);  
  -- the first 2str must 18,19,20,21,22  
  if (substr(v_date,1,2) >= '18' and substr(v_date,1,2) <= '22' ) then  
    if (v_len >= 4 and v_len <=8) then  
      case v_len  
      when 4 then  
        return (v_date||'0101')::date;  
      when 6 then  
        return (v_date||'01')::date;  
      when 8 then  
        return v_date::date;  
      else  
        return '19700101'::date;  
      end case;  
    else   
      -- not a valid date  
      return '22220101';  
    end if;  
  else  
    -- not a valid date  
    return '22220101';  
  end if;  
exception when others then  
  raise notice 'partition table name date format error.';  
  return '19700101'::date;  
end;  
$$ language plpgsql;  

参考

1. http://blog.163.com/digoal@126/blog/static/16387704020130433036377/

digoal’s 大量PostgreSQL文章入口