postgresql 分区表 创建函数

postgresql中，并没有分区表的创建命令，是通过创建继承表及约束等规则来创建，步骤繁琐且麻烦，邮件中封装了一个方法。便于创建分区表

@H_874_3@

规范：

postgresql中 时间分区字段请统一采用timestamp(0) 类型

一、原始表tbl_partition

二、将普通表转换为分区表（public.partition_generate）

@H_874_3@

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 $$

@H_673_101@importre

@H_673_101@importdatetiR_407_11845@e

@H_673_101@defudf_date_add(lstr,day):

s = datetiR_407_11845@e.datetiR_407_11845@e.strptime(lstr,"%Y%m%d")

s = s+datetiR_407_11845@e.timedelta(days=day)

@H_673_101@returnstr(s).replace('-',0)">'')[0:8]

startdate=start_date

enddate=END_DATE

@H_673_101@ifptype@H_673_101@not@H_673_101@in('@H_664_146@mon',0)">'day'):

@H_673_101@return"error:\tptypeonly support '@H_892_83@mon'、'day'"

@H_673_101@ifptype=='day':

@H_673_101@if@H_673_101@notre.match('[0-9]{8}',startdatE):

"error:\tstartdateneed 20130101 format"

:rgb(4,enddatE):

"error:\tenddateneed 20130101 format"

@H_673_101@try:

table_name = tablename.lower().split('.')[1]

table_scheR_407_11845@a = tablename.lower().split(0]

@H_673_101@except(IndexError):

'error:\ttablenameneed "tablescheR_407_11845@a.table_name" format'

@H_874_3@

whileTrue:

#1)创建继承表

sql ="""create table """+table_scheR_407_11845@a+"""."""+table_name+"""_"""+startdate+""" (

check ( """+pcolumn+""" >= DATE '"""+udf_date_add(startdate,-1)+"""' AND """+pcolumn+""" < DATE '"""+startdate+"""' )

) inheritS ("""+table_scheR_407_11845@a+"""."""+table_name+""")"""

#plpy.info(sql)

@H_673_101@try:

plpy.execute(sql)

@H_673_101@except:

@H_673_101@pass

#2)创建索引

sql ="""create index """+table_name+"""_"""+pcolumn+""" on """+table_scheR_407_11845@a+""" ("""+pcolumn+""")"""

pass

@H_874_3@

startdate=udf_date_add(startdate,1)

@H_673_101@ifstartdate>enddate:

@H_673_101@break

#2.0)创建错误表

sql ="""create table """+table_name+"""_error_"""+pcolumn+""" as SELEct * from """+table_name+""" limit 0

"""

@H_673_101@try:

plpy.execute(sql)

except:

@H_673_101@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):

@H_673_101@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)

@H_673_101@pass

"success"

$$ LANGUAGE plpythonu;