postgresql分區表建立

標籤：分區表

postgresql中，並沒有分區表的建立命令，是通過建立繼承表及約束等規則來建立，步驟繁瑣且麻煩，封裝了一個方法。便於建立分區表:

CREATE TABLE "odl"."user_action_fatt0" ("date_id" numeric(8,0),"chnl_id" numeric(2,0),"user_acct_type" numeric(2,0),"user_id" numeric(19,0),"cont_id" numeric(19,0),"act_id" numeric(5,0),"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 start_date varchar, IN end_date varchar, IN ptype varchar, IN pcolumn varchar) OWNER TO "brecom";

postgresql分區表建立