注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

星期五

明天不上班

 
 
 

日志

 
 
关于我

一个特立独行的Java程序员,比较宅,上上网,写博客,听音乐,看电影。

网易考拉推荐

使用PL/Python重写partition函数  

2012-05-31 01:16:45|  分类: Postgresql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
仅仅是一个练习,就是为熟悉PL/Python语法
之前PL/SQL的例子
http://zhenghaoju700.blog.163.com/blog/static/13585951820124176472832/

ENV: Ubuntu 12.04, Postgresql 9.1

CREATE OR REPLACE FUNCTION create_partitions_tables_plpython (p_current_date text, p_table_name text)
RETURNS void
AS $$
from string import Template
v_current_date = p_current_date
v_table_name = p_table_name

#create partition table
v_partition_table_name = v_table_name + "_" + v_current_date
v_sql = Template("CREATE TABLE ${partition_table_name} (CHECK (date_trunc( 'day', time_stamp) = DATE '${current_date}' )) INHERITS (${table_name})")

v_sql_str = v_sql.substitute(partition_table_name=v_partition_table_name, current_date=v_current_date, table_name=v_table_name)
plpy.notice(v_sql_str)
plpy.execute(v_sql_str)
#get OID of table name
records = plpy.execute("SELECT oid FROM pg_class WHERE relname = '" + v_table_name + "'", 1)
v_table_oid = records[0]['oid']

#add PK for partion table
v_pks = []
records = plpy.execute("SELECT conindid FROM pg_constraint WHERE conrelid = " + v_table_oid + " AND contype = 'p'", 1)
v_pk_oid = records[0]['conindid']

records = plpy.execute("SELECT attname FROM pg_attribute WHERE attrelid = " + v_pk_oid)
for record in records:
v_pks.append(record['attname'])


if len(v_pks) != 0:
v_pk_str = ",".join(v_pks)
v_pk_name = v_partition_table_name + "_pkey"
v_sql = Template("ALTER TABLE ${partition_table_name} ADD CONSTRAINT ${pk_name} primary key (${pk_str})")
v_sql_str = v_sql.substitute(partition_table_name=v_partition_table_name, pk_name=v_pk_name, pk_str=v_pk_str)
plpy.execute(v_sql_str)

#create index for partition table
v_count = 0
records = plpy.execute("SELECT indexrelid FROM pg_index WHERE indrelid = " + v_table_oid + " AND indisprimary != 't'")
for record in records:
v_indexes = []
v_index_oid = record['indexrelid']

records2 = plpy.execute("SELECT attname FROM pg_attribute WHERE attrelid = " + v_index_oid)
for record2 in records2:
v_indexes.append(record2['attname'])

v_index_str = ",".join(v_indexes)
v_index_name = v_partition_table_name + "_index_" + v_count
v_sql = Template("CREATE INDEX ${index_name} ON ${partition_table_name} ( ${index_str} )")
v_sql_str = v_sql.substitute(index_name=v_index_name, partition_table_name=v_partition_table_name, index_str=v_index_str)
plpy.execute(v_sql_str)
v_count = v_count + 1

#create rule for partition table
v_rule_name = v_partition_table_name + '_insert'
v_sql = Template("CREATE RULE ${rule_name} AS ON INSERT TO ${table_name} WHERE (date_trunc( 'day', time_stamp) = DATE '${current_date}') DO INSTEAD INSERT INTO ${partition_table_name} VALUES (NEW.*)")
v_sql_str = v_sql.substitute(rule_name=v_rule_name, table_name=v_table_name, current_date=v_current_date, partition_table_name=v_partition_table_name)

plpy.execute(v_sql_str)

$$ LANGUAGE plpythonu;

总结:
如果你熟悉Python语法的话建议使用PL/Python,Python强大的函数库,会另你非常爽的, 但是如果一定要为了保持可迁移性,最好选择PL/SQL (语法和Oracle PL/SQL语法相似)

在此在分享一下PPT(第二部分 有讲PL/Python)

参考资料:
postgreSQL9.1 DOC 对PL/Python介绍很详细!!!
http://www.postgresql.org/docs/9.1/static/plpython.html
  评论这张
 
阅读(830)| 评论(0)
推荐

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017