大佬教程收集整理的这篇文章主要介绍了postgresql分区表创建,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
postgresql中,并没有分区表的创建命令,是通过创建继承表及约束等规则来创建,步骤繁琐且麻烦,封装了一个方法。便于创建分区表:
CREATE table "odl"."user_action_fatt0" ( "date_ID" numeric(8,0),"chnl_ID" numeric(2,"user_acct_type" numeric(2,"user_iD" numeric(19,"cont_ID" numeric(19,"act_ID" numeric(5,"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 datetiR_998_11845@e
def @H_502_73@udf_date_add(lstr,day):
s = datetiR_998_11845@e.datetiR_998_11845@e.strptime(lstr,"%Y%m%d")
s = s+datetiR_998_11845@e.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_scheR_998_11845@a = tablename.lower().split('.')[0]
except (IndexError):
return 'error:\ttablename need "tablescheR_998_11845@a.table_name" format'
while True:
#1)create the partition table
sql = """create table """+table_scheR_998_11845@a+"""."""+table_name+"""_"""+startdate+""" ( check (""" + pcolumn + """ >= (""" + startdate + """::numeriC) AND """ + pcolumn + """ < (""" + udf_date_add(startdate,1) + """::numeriC)) ) inheritS ("""+table_scheR_998_11845@a+"""."""+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_scheR_998_11845@a+"""."""+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_scheR_998_11845@a+"""."""+table_name+"""_error_"""+pcolumn+""" as SELEct * from """+table_scheR_998_11845@a+"""."""+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_scheR_998_11845@a+"""."""+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_scheR_998_11845@a+"""."""+table_name+"""_error_"""+pcolumn+""" VALUES (NEW.*); end if; """
trigger_tmp=trigger_tmp[3:]
sql =""" CREATE OR replaCE FUNCTION """+table_scheR_998_11845@a+"""."""+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_scheR_998_11845@a+"""."""+table_name+""" FOR EACH ROW EXECUTE PROCEDURE """+table_scheR_998_11845@a+"""."""+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 pcolumn varchar) owneR TO "brecom";
以上是大佬教程为你收集整理的postgresql分区表创建全部内容,希望文章能够帮你解决postgresql分区表创建所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。