查询语句：
postgres=# select attrelid::regclass,attname 
from pg_attribute a ,pg_class b
where a.attrelid=b.oid
and b.relnamespace=11
and atttypid=26
and b.relstorage=‘h‘
and attname=‘oid‘
and b.relname not like ‘%index‘;
        attrelid         | attname 
-------------------------+---------
 pg_proc                 | oid
 pg_type                 | oid
 pg_class                | oid
 pg_attrdef              | oid
 pg_constraint           | oid
 pg_operator             | oid
 pg_opfamily             | oid
 pg_opclass              | oid
 pg_am                   | oid
 pg_amop                 | oid
 pg_amproc               | oid
 pg_language             | oid
 pg_largeobject_metadata | oid
 pg_rewrite              | oid
 pg_trigger              | oid
 pg_event_trigger        | oid
 pg_cast                 | oid
 pg_enum                 | oid
 pg_namespace            | oid
 pg_conversion           | oid
 pg_database             | oid
 pg_tablespace           | oid
 pg_authid               | oid
 pg_ts_config            | oid
 pg_ts_dict              | oid
 pg_ts_parser            | oid
 pg_ts_template          | oid
 pg_extension            | oid
 pg_foreign_data_wrapper | oid
 pg_foreign_server       | oid
 pg_user_mapping         | oid
 pg_default_acl          | oid
 pg_collation            | oid
 pg_resqueue             | oid
 pg_resqueuecapability   | oid
 pg_resourcetype         | oid
 pg_resgroup             | oid
 pg_resgroupcapability   | oid
 pg_extprotocol          | oid
 pg_partition            | oid
 pg_partition_rule       | oid
 pg_compression          | oid
(42 rows)

postgres=# 

postgres=#  select * from pg_attribute a where atttypid=26 limit 1;
-[ RECORD 1 ]-+-------------
attrelid      | 1255
attname       | pronamespace
atttypid      | 26
attstattarget | -1
attlen        | 4
attnum        | 2
attndims      | 0
attcacheoff   | -1
atttypmod     | -1
attbyval      | t
attstorage    | p
attalign      | i
attnotnull    | t
atthasdef     | f
attisdropped  | f
attislocal    | t
attinhcount   | 0
attcollation  | 0
attacl        | 
attoptions    | 
attfdwoptions | 

postgres=# \x
Expanded display is on.
postgres=#  select * from pg_class a where relnamespace=11 limit 1;
-[ RECORD 1 ]--+--------------------------------
relname        | pg_attribute_relid_attnam_index
relnamespace   | 11
reltype        | 0
reloftype      | 0
relowner       | 10
relam          | 403
relfilenode    | 0
reltablespace  | 0
relpages       | 8
reltuples      | 3367
relallvisible  | 0
reltoastrelid  | 0
relhasindex    | f
relisshared    | f
relpersistence | p
relkind        | i
relstorage     | h
relnatts       | 2
relchecks      | 0
relhasoids     | f
relhaspkey     | f
relhasrules    | f
relhastriggers | f
relhassubclass | f
relispopulated | t
relreplident   | n
relfrozenxid   | 0
relminmxid     | 0
relacl         | 
reloptions     | 

如何识别对象

1、识别临时表、UNLOGGED TABLE、临时表

select relname from pg_class where relpersistence=? and relkind=‘r‘;  
pg_class 的relpersistence用于识别表是什么表(正常表、不记日志表、临时表)。 relkind用于识别是什么对象类别(表、索引、序列、切片、视图、物化视图、复合类型、外部表、分区表)。

relpersistence    
        p = permanent table, u = unlogged table, t = temporary table  
relkind   
        r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table  

Greenplum 扩展

pg_class.relstorage 用于区分是什么存储

h = 堆表(heap)  
a = append only row存储表  
c = append only column存储表 

存储过程

pg_proc

数据库

pg_database

表空间

pg_tablespace

schema

pg_namespace

用户

pg_roles

索引接口

pg_am

如何获取对象定义

使用这些函数接口，可以获得对应对象的定义。

pg_get_indexdef  

pg_get_functiondef  

pg_get_triggerdef  

pg_get_ruledef  

pg_get_viewdef  

pg_get_constraintdef 

postgres=# select * from pg_get_indexdef(‘idx_tbl2_1‘::regclass);  
                 pg_get_indexdef                    
--------------------------------------------------  
 CREATE INDEX idx_tbl2_1 ON tbl2 USING btree (id)  
(1 row)  

将oid 转换成名称的集中类型

举例子

postgres=# select 1259::regclass;
 regclass 
----------
 pg_class
(1 row)

postgres=# select oid,relname from pg_class where oid=‘pg_class‘::regclass;
 oid  | relname  
------+----------
 1259 | pg_class
(1 row)

postgres=# select oid::regoper,oid::regoperator,oid,oprname from pg_operator limit 1;
     oid      |        oid        | oid | oprname 
--------------+-------------------+-----+---------
 pg_catalog.= | =(integer,bigint) |  15 | =
(1 row)

postgres=# select oid:: regoper,oid::regoperator,oid,oprname from pg_operator limit 1;
     oid      |        oid        | oid | oprname 
--------------+-------------------+-----+---------
 pg_catalog.= | =(integer,bigint) |  15 | =
(1 row)

Greenplum6 数据库数据库学习_数据字典