postgresql 分区表 创建函数
postgresql中,并没有分区表的创建命令,是通过创建继承表及约束等规则来创建,步骤繁琐且麻烦,邮件中封装了一个方法。便于创建分区表
规范:
postgresql中 时间分区字段请统一采用timestamp(0) 类型
一、原始表tbl_partition
create table tbl_partition (
id integer,
name varchar(20),
gender boolean,
yyyymmdd timestamp(0),
dept char(4)
);
二、将普通表转换为分区表(public.partition_generate)
select public.partition_generate('public.tbl_partition','20130101','20130103','day','yyyymmdd');
参数说明:
三、创建结果(其中一个是错误表,用于存放不能映射的分区)
四、验证数据
五、源码
CREATEorREPLACEFUNCTIONpublic.partition_generate(tablenamevarchar,start_date varchar,end_date varchar,ptype varchar,pcolumn varchar) RETURNS text AS $$ importre importdatetime defudf_date_add(lstr,day): s = datetime.datetime.strptime(lstr,"%Y%m%d") s = s+datetime.timedelta(days=day) returnstr(s).replace('-',0)">'')[0:8]
startdate=start_date enddate=end_date
ifptypenotin('mon',0)">'day'): return"error:tptypeonly support 'mon'、'day'" ifptype=='day': ifnotre.match('[0-9]{8}',startdate): "error:tstartdateneed 20130101 format" "error:tenddateneed 20130101 format" try: table_name = tablename.lower().split('.')[1] table_schema = tablename.lower().split(0] except(IndexError): 'error:ttablenameneed "tableschema.table_name" format' whileTrue: #1)创建继承表 sql ="""create table """+table_schema+"""."""+table_name+"""_"""+startdate+""" ( check ( """+pcolumn+""" >= DATE '"""+udf_date_add(startdate,-1)+"""' AND """+pcolumn+""" < DATE '"""+startdate+"""' ) ) INHERITS ("""+table_schema+"""."""+table_name+""")""" #plpy.info(sql) try: plpy.execute(sql) except: pass #2)创建索引 sql ="""create index """+table_name+"""_"""+pcolumn+""" on """+table_schema+""" ("""+pcolumn+""")""" pass startdate=udf_date_add(startdate,1) ifstartdate>enddate: break
#2.0)创建错误表 sql ="""create table """+table_name+"""_error_"""+pcolumn+""" as select * from """+table_name+""" limit 0 """ try: plpy.execute(sql) except: pass
#3)创建触发器函数 trigger_tmp="" startdate=start_date
: trigger_tmp=trigger_tmp+"""elsif(NEW."""+pcolumn+""">=DATE '"""+udf_date_add(startdate,-1)+"""' and NEW."""+pcolumn+""" < DATE '"""+startdate+"""' ) THEN INSERT INTO """+table_name+"""_"""+startdate+""" VALUES (NEW.*); """ startdate=udf_date_add(startdate,255)">ifstartdate>udf_date_add(enddate,0)">365): break
trigger_tmp=trigger_tmp+""" else +pcolumn+""" VALUES (NEW.*); end if; """ trigger_tmp=trigger_tmp[3:]
sql = CREATE OR REPLACE FUNCTION """+table_name+"""_insert_trigger() RETURNS TRIGGER AS $PROC$ BEGIN """+trigger_tmp+""" RETURN NULL; END; LANGUAGEplpgsql """
#plpy.info(sql) plpy.execute(sql)
#4)创建触发器
sql = CREATE TRIGGER insert_"""+table_name+"""_trigger BEFORE INSERT ON """+table_name+""" FOR EACH ROW EXECUTE PROCEDURE """+table_name+"""_insert_trigger() """ #plpy.info(sql) pass
"success"
$$ LANGUAGE plpythonu; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |