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";</pre>