GP中查询表和视图清单，视图的字段及其类型

查询schema_name.view_name的字段及其类型：

select a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as type,a.attnotnullfrom pg_catalog.pg_attribute a,pg_catalog.pg_class c, pg_catalog.pg_namespace nwhere  a.attrelid=c.oidand c.relname='view_name'and a.attnum>0 AND NOT a.attisdroppedand n.oid = c.relnamespaceand n.nspname='schema_name'order by a.attnum

查询指定schema下view的清单：

select c.relnamefrom pg_catalog.pg_class c, pg_catalog.pg_namespace nwheren.oid = c.relnamespaceand n.nspname='schema_name'and  c.relkind='v'

查询指定schema下所有table的清单：

select c.relnamefrom pg_catalog.pg_class c, pg_catalog.pg_namespace nwheren.oid = c.relnamespaceand n.nspname='schema_name'and  c.relkind='r'

查询指定schema下所有外部表的清单：

select c.relnamefrom pg_catalog.pg_class c, pg_catalog.pg_namespace nwheren.oid = c.relnamespaceand n.nspname='schema_name'and  c.relkind='r'  and relstorage='x'

查询指定schema下所有普通表的清单：

select c.relnamefrom pg_catalog.pg_class c, pg_catalog.pg_namespace nwheren.oid = c.relnamespaceand n.nspname='schema_name'and  c.relkind='r'  and relstorage in ('h', 'a');

注意，relstorage字段取值“x”表示外部表，“h”表示heap表，“a”表示append-only表，“v”表示virtual表。

查询指定schema_name下table_owner的表：

select * from(select c.relname, pg_catalog.pg_get_userbyid(c.relowner) as table_ownerfrom pg_catalog.pg_class c, pg_catalog.pg_namespace nwhere n.oid = c.relnamespaceand n.nspname='schema_name') t where t.table_owner = 'table_owner';

查询指定指定table_owner的所有普通表：

select * from(select n.nspname, c.relname, pg_catalog.pg_get_userbyid(c.relowner) as table_ownerfrom pg_catalog.pg_class c, pg_catalog.pg_namespace nwhere n.oid = c.relnamespace and  c.relkind='r' and relstorage in ('h', 'a')) t where t.table_owner = 'table_owner';