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