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

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

select a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as type,a.attnotnull
from pg_catalog.pg_attribute a,pg_catalog.pg_class c, pg_catalog.pg_namespace n
where  a.attrelid=c.oid
and c.relname='view_name'
and a.attnum>0 AND NOT a.attisdropped
and n.oid = c.relnamespace
and n.nspname='schema_name'
order by a.attnum

查询指定schema下view的清单：

select c.relname
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where
n.oid = c.relnamespace
and n.nspname='schema_name'
and  c.relkind='v'

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

select c.relname
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where
n.oid = c.relnamespace
and n.nspname='schema_name'
and  c.relkind='r'

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

select c.relname
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where
n.oid = c.relnamespace
and n.nspname='schema_name'
and  c.relkind='r'  and relstorage='x'

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

select c.relname
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where
n.oid = c.relnamespace
and 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_owner
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where n.oid = c.relnamespace
and 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_owner
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where n.oid = c.relnamespace and  c.relkind='r' and relstorage in ('h', 'a')
) t where t.table_owner = 'table_owner';