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