本篇文章主要介绍了" GreenPlum中自定义查询表分区的视图"，主要涉及到方面的内容，对于其他数据库感兴趣的同学可以参考一下：
GreenPlum中查询分区表的信息本身并不是特别的友好，需要做表关联并且做相应的处理，为了后期维护起来方便，这里创建两个视图供DBA直接查询，非常的方便。...
GreenPlum中查询分区表的信息本身并不是特别的友好，需要做表关联并且做相应的处理，为了后期维护起来方便，这里创建两个视图供DBA直接查询，非常的方便。
1、创建list分区表的视图
create or replace view v_gp_list_partition_meta
as
SELECT
pp.parrelid::regclass table_name,
pr1.parchildrelid::regclass child_tbl_name,
pr1.parname as partition_name,
pr1.parruleord as partitionposition,
translate(pg_get_expr(pr1.parlistvalues,pr1.parchildrelid),'-'':date character varying bpchar numeric double percision timestamp without time zone','') as partitionlistvalue,
substring(parlistvalues,'consttype ([0-9]+)')::integer::regtype listtype
FROM pg_partition pp, pg_partition_rule pr1
where pp.paristemplate = false and pr1.paroid=pp.oid and pp.parkind = 'l';
2、创建range分区表的视图
create or replace view v_gp_range_partition_meta
as
SELECT
pp.parrelid::regclass table_name,
pr1.parchildrelid::regclass child_tbl_name,
pr1.parname as partition_name,
pr1.parruleord as partitionposition,
translate(pg_get_expr(pr1.parrangestart,pr1.parchildrelid),'-'':date character varying bpchar numeric double percision timestamp without time zone','') as partitionrangestart,
translate(pg_get_expr(pr1.parrangeend,pr1.parchildrelid),'-'':date character varying bpchar numeric double percision timestamp without time zone','') as partitionrangeend,
substring(parrangeend,'consttype ([0-9]+)')::integer::regtype rangetype
FROM pg_partition pp, pg_partition_rule pr1
where pp.paristemplate = false and pr1.paroid=pp.oid and pp.parkind = 'r';
3、依次查询两个视图
testdb=# select * from v_gp_list_partition_meta;
table_name | child_tbl_name | partition_name | partitionposition | partitionlistvalue | listtype
-----------------------------+---------------------------------------------+----------------+-------------------+--------------------+----------
tbl_partition_list_yyyymmdd | tbl_partition_list_yyyymmdd_1_prt_p20160718 | p20160718 | 1 | 20160718 | date
tbl_partition_list_yyyymmdd | tbl_partition_list_yyyymmdd_1_prt_p20160719 | p20160719 | 2 | 20160719 | date
(2 rows)
testdb=# select * from v_gp_range_partition_meta;
table_name | child_tbl_name | partition_name | partitionposition | partitionlistvalue | listtype
-----------------------------+---------------------------------------------+----------------+-------------------+--------------------+----------
tbl_partition_range_yyyymmdd | tbl_partition_range_yyyymmdd_1_prt_p20160718 | p20160718 | 1 | 20160718 | 20160719 | date
tbl_partition_range_yyyymmdd | tbl_partition_range_yyyymmdd_1_prt_p20160719 | p20160719 | 2 | 20160719 | 20160720 | date
(121 rows)
以上就介绍了 GreenPlum中自定义查询表分区的视图，包括了方面的内容，希望对其他数据库有兴趣的朋友有所帮助。
本文网址链接：http://www.codes51.com/article/detail_2775098.html
