postgresql 分区表 创建函数

postgresql中，并没有分区表的创建命令，是通过创建继承表及约束等规则来创建，步骤繁琐且麻烦，邮件中封装了一个方法。便于创建分区表

规范：

postgresql中 时间分区字段请统一采用  timestamp(0) 类型

一、原始表 tbl_partition

create table tbl_partition (

id integer,

name varchar(20),

gender boolean,

yyyymmdd timestamp(0),

dept char(4)

);

二、将普通表转换为分区表（public.partition_generate）

select public.partition_generate('public.tbl_partition','20130101','20130103','day','yyyymmdd');

参数说明：

1. 表名 schema.tablename 格式
2. 创建分区的起始时间
3. 创建分区的结束时间
4. 分区类型 day：天分区  mon：月分区
5. 分区字段

三、创建结果(其中一个是错误表，用于存放不能映射的分区）

四、验证数据

五、源码

CREATE or REPLACE FUNCTION public.partition_generate(tablename varchar,start_date varchar,end_date varchar,ptype varchar,pcolumn varchar)

    RETURNS text

AS $$

    import re

    import datetime

    def udf_date_add(lstr,day):

        s = datetime.datetime.strptime(lstr, "%Y%m%d")

        s = s+datetime.timedelta(days=day)

        return str(s).replace('-','')[0:8]

    

    startdate=start_date

    enddate=end_date

    

    if ptype not in('mon','day'):

        return "error:\tptype only support 'mon'、'day'"

    if ptype=='day':

        if not re.match('[0-9]{8}',startdate):

            return "error:\tstartdate need 20130101 format"

        if not re.match('[0-9]{8}',enddate):

            return "error:\tenddate need 20130101 format"

    try:                                                                                                      

        table_name = tablename.lower().split('.')[1]

        table_schema = tablename.lower().split('.')[0]                                                

    except (IndexError):                                    

        return 'error:\ttablename need "tableschema.table_name" format' 

    while True:

        #1)创建继承表

        sql = """create table """+table_schema+"""."""+table_name+"""_"""+startdate+""" (

                    check ( """+pcolumn+""" >= DATE '"""+udf_date_add(startdate,-1)+"""' AND """+pcolumn+""" < DATE '"""+startdate+"""' )       

                    ) INHERITS ("""+table_schema+"""."""+table_name+""")"""

        #plpy.info(sql)

        try:

            plpy.execute(sql)

        except:

            pass

        #2)创建索引

        sql = """create index """+table_name+"""_"""+startdate+"""_"""+pcolumn+""" on """+table_schema+"""."""+table_name+"""_"""+startdate+""" ("""+pcolumn+""")"""

        #plpy.info(sql)

        try:

            plpy.execute(sql)

        except:

            pass

        startdate=udf_date_add(startdate,1)

        if startdate>enddate:

            break 

    

    #2.0)创建错误表    

    sql = """create table """+table_schema+"""."""+table_name+"""_error_"""+pcolumn+""" as select * from """+table_schema+"""."""+table_name+""" limit 0

    """

    try:

        plpy.execute(sql)

    except:

        pass

    

    #3)创建触发器函数

    trigger_tmp="" 

    startdate=start_date

    

    while True:

        trigger_tmp=trigger_tmp+"""elsif (NEW."""+pcolumn+""">=DATE '"""+udf_date_add(startdate,-1)+"""' and NEW."""+pcolumn+""" < DATE '"""+startdate+"""' ) THEN  

                INSERT INTO """+table_schema+"""."""+table_name+"""_"""+startdate+""" VALUES (NEW.*); 

            """

        startdate=udf_date_add(startdate,1)

        if startdate>udf_date_add(enddate,365):

            break

        

    trigger_tmp=trigger_tmp+"""

            else  

                INSERT INTO """+table_schema+"""."""+table_name+"""_error_"""+pcolumn+""" VALUES (NEW.*);   

            end if;

                    """

    trigger_tmp=trigger_tmp[3:]

    

    sql ="""

     CREATE OR REPLACE FUNCTION """+table_schema+"""."""+table_name+"""_insert_trigger()                      

        RETURNS TRIGGER AS 

        $PROC$

        BEGIN  

            """+trigger_tmp+"""

            RETURN NULL;  

        END;  

        $PROC$

        LANGUAGE plpgsql

    """

    

    #plpy.info(sql)

    plpy.execute(sql)

    

    #4)创建触发器

    

    sql = """

    CREATE TRIGGER insert_"""+table_name+"""_trigger

    BEFORE INSERT ON """+table_schema+"""."""+table_name+"""

    FOR EACH ROW EXECUTE PROCEDURE """+table_schema+"""."""+table_name+"""_insert_trigger()

    """

    #plpy.info(sql)

    try:

        plpy.execute(sql)

    except:

        pass

    

    return "success"

    

$$ LANGUAGE plpythonu;