postgresql分区表创建
来源:互联网 发布:js获取当前unix时间戳 编辑:程序博客网 时间:2024/06/09 13:38
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";
1 0
- postgresql 分区表 创建函数
- postgresql分区表创建
- postgresql根据表创建对应的分区表
- PostgreSQL 创建分区表,SQL优化之PostgreSQL Table Partitioning
- postgresql分区表
- postgresql 中分区表的创建及使用样例
- postgresql分区表笔记
- POSTGRESQL 分区表初次体验
- 转载:PostgreSQL的分区表
- postgresql分区表-触发器
- postgresql 建立删除分区表
- Postgresql 实现分区表
- postgresql分区表1
- postgresql分区表2
- postgresql分区表的使用
- Postgresql 分区表测试
- 【PostgreSQL-9.6.3】分区表
- PostgreSQL分区表相关
- 黑马程序员--数组
- Linux下禁止ping方法
- SQL SERVER中ROLLUP 运算符的用法
- Prototype Pattern(原型模式)
- C++ const 与 重载
- postgresql分区表创建
- Android 异步开发之 AsyncQueryHandler
- ssl握手过程
- 祭奠我死去的CET-6
- 创建进程 并隐藏DOS窗口
- 使用容器
- wc命令
- Wijmo 更优美的jQuery UI部件集:复合图表(CompositeChart)
- 哈弗曼树的绘制