| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
wdey
9年前发布

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

 本文由用户 wdey 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1432472114500.html
PostgreSQL 数据库服务器