postgresql中，并没有分区表的创建命令，是通过创建继承表及约束等规则来创建，，步骤繁琐且麻烦，封装了一个方法。便于创建分区表:
.”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 reimport datetime:s = datetime.datetime.strptime(lstr, “%Y%m%d”)s = s+datetime.timedelta(days=day)return str(s).replace(‘-‘,”)[0:8]startdate=start_dateenddate=end_dateif ptype not in(‘mon’,’day’):ptype==’day’:if not re.match(‘[0-9]{8}’,startdate):re.match(‘[0-9]{8}’,enddate)::table_name = tablename.lower().split(‘.’)[1]table_schema = tablename.lower().split(‘.’)[0]except (IndexError)::#1)create the partition tablesql = “””create table “””+table_schema+”””.”””+table_name+”””_”””+startdate+””” (check (“”” + pcolumn + “”” >= (“”” + startdate + “””::numeric) AND “”” + pcolumn + “”” < (“”” + udf_date_add(startdate,1) + “””::numeric))) INHERITS (“””+table_schema+:plpy.execute(sql)except:pass#2)create the index for the partition tablesql = :plpy.execute(sql)except:passstartdate=udf_date_add(startdate,1)if startdate>enddate:break#2.0)create the error tablesql = “””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 tabletrigger_tmp=””startdate=start_datewhile True:trigger_tmp=trigger_tmp+”””elsif (NEW.”””+pcolumn+””” >= (“””+startdate+”””::numeric) and NEW.”””+pcolumn+””” < (“””+udf_date_add(startdate,1)+”””::numeric) ) THENINSERT INTO “””+table_schema+”””.”””+table_name+”””_”””+startdate+””” VALUES (NEW.*);”””startdate=udf_date_add(startdate,1)if startdate>udf_date_add(enddate,365):breaktrigger_tmp=trigger_tmp+”””elseINSERT 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 triggersql = “””CREATE TRIGGER insert_”””+table_name+”””_triggerBEFORE 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:$BODY$LANGUAGE plpythonuCOST 100CALLED ON NULL INPUTSECURITY INVOKERVOLATILE;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”;
人之相识，贵在相知；人之相知，贵在知心。
