postgresql分区表创建

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

CREATE TABLE "odl"."user_action_fatt0" ( "date_id" numeric(8,0),"chnl_id" numeric(2,"user_acct_type" numeric(2,"user_id" numeric(19,"cont_id" numeric(19,"act_id" numeric(5,"act_value" numeric )

创建分区函数

CREATE FUNCTION "public"."partition_generate_numeric"(IN tablename varchar,IN start_date varchar,IN end_date varchar,IN ptype varchar,IN pcolumn varchar) RETURNS "text" 
    AS $BODY$
    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' or '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)create the partition table
        sql = """create table """+table_schema+"""."""+table_name+"""_"""+startdate+""" ( check (""" + pcolumn + """ >= (""" + startdate + """::numeric) AND """ + pcolumn + """ < (""" + udf_date_add(startdate,1) + """::numeric)) ) INHERITS ("""+table_schema+"""."""+table_name+""")"""
        #plpy.info(sql)
        try:
            plpy.execute(sql)
        except:
            pass
        #2)create the index for the partition table
        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)create the error table
    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)create the trigger for the partition table
    trigger_tmp="" 
    startdate=start_date

    while True:
        trigger_tmp=trigger_tmp+"""elsif (NEW."""+pcolumn+""" >= ("""+startdate+"""::numeric) and NEW."""+pcolumn+""" < ("""+udf_date_add(startdate,1)+"""::numeric) ) 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)create the insert trigger

    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"

$BODY$
    LANGUAGE plpythonu
    COST 100
    CALLED ON NULL INPUT
    SECURITY INVOKER
    VOLATILE;
ALTER FUNCTION "public"."partition_generate_numeric"(IN tablename varchar,IN pcolumn varchar) OWNER TO "brecom";