View of custom query table partitions in Greenplum

Greenplum Query partition table information itself is not particularly friendly, need to do table association and do the corresponding processing, in order to facilitate later maintenance, here to create two views for the DBA to directly query, very convenient.

1. Create a view of the list partition table create or replace view v_gp_list_partition_metaas 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), '-':d ate character varying bpchar numeric double percision  timestamp without time zone ', ')  as partitionlistvalue,substring (Parlistvalues, ' consttype  ([0-9]+) '):: Integer::regtype listtypefrom  pg_partition pp, pg_ Partition_rule pr1where pp.paristemplate = false and pr1.paroid=pp.oid and  pp.parkind =  ' l '; 2. Create a view of the range partition table Create or replace view v_gp_range_ Partition_metaas 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) , '-':d ate character varying bpchar numeric double percision timestamp  without time zone ', ')  as partitionrangestart,translate (pg_get_expr (Pr1.parrangeend, Pr1.parchildrelid), '-':d ate character varying bpchar numeric double percision  timestamp without time zone ', ')  as partitionrangeend,substring (Parrangeend, ' consttype  ([0-9]+) '):: Integer::regtype rangetypefrom  pg_partition pp, pg_ Partition_rule pr1where pp.paristemplate = false and pr1.paroid=pp.oid and  pp.parkind =  ' R '; 3, query Two views in turn 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)

This article from "Brave forward, resolutely left" blog, declined reprint!

View of custom query table partitions in Greenplum