获取Greenplum 的元数据信息，schema下面的表和列信息

查询指定schema下面的表

select '|' || t.tablename as tab_name,

coalesce(replace(obj_description((t.SCHEMANAME||'."'||t.TABLENAME||'"')

::regclass),

'|',

''),'') || '|' AS tab_desc

from pg_tables t

where t.schemaname = 'dcf_user'

and not exists (select 'x'

from pg_partitions s

where s.partitiontablename = t.tablename);

查询指定的表下面的列信息

SELECT '|'|| aa.COL_NAME AS column_name,

(CASE aa.COL_TYPE

WHEN 'character' THEN

'char'

WHEN 'character varying' THEN

'varchar'

WHEN 'timestamp without time zone' THEN

'timestamp'

ELSE

aa.COL_TYPE

END) AS data_type,

aa.COL_LEN AS data_length,

aa.COL_PREC AS data_precision,

bb.COL_DESC AS comments,

(CASE aa.COL_NULLABLE

WHEN 'NO' THEN

'0'

ELSE

'1'

END) AS nullable,

(CASE COALESCE(CC.COLUMN_NAME,'0')

WHEN '0' THEN

'0'

ELSE

'1'

END) || '|' AS key_flag

FROM (SELECT TABLE_NAME,

A.ORDINAL_POSITION AS COL_NUM,

A.COLUMN_NAME AS COL_NAME,

A.DATA_TYPE AS COL_TYPE,

CASE WHEN A.DATA_TYPE='numeric' THEN A.NUMERIC_PRECISION

ELSE A.CHARACTER_MAXIMUM_LENGTH END COL_LEN,

A.NUMERIC_SCALE AS COL_PREC,

A.DATETIME_PRECISION AS COL_TIME_PREC,

A.IS_NULLABLE AS COL_NULLABLE,

A.COLUMN_DEFAULT AS COL_DEFAULT,

A.TABLE_SCHEMA AS TABLE_SCHEMA

FROM INFORMATION_SCHEMA.COLUMNS A

WHERE A.TABLE_SCHEMA = 'dcf_user'

AND A.table_name = 'a_rkcj') AS AA

LEFT JOIN (SELECT t2.RELNAME table_name,

t1.OBJSUBID,

t1.DESCRIPTION AS COL_DESC

FROM PG_DESCRIPTION t1

INNER JOIN PG_STAT_ALL_TABLES t2 ON t1.OBJOID = t2.RELID

WHERE t2.SCHEMANAME = 'dcf_user'

AND T2.RELNAME = 'a_rkcj') AS BB ON (AA.COL_NUM =

BB.OBJSUBID AND

AA.table_name =

bb.table_name)

LEFT JOIN (select pg_namespace.nspname as table_schema,

pg_class.relname as TABLE_NAME,

pg_attribute.attname as COLUMN_NAME

from pg_constraint

inner join pg_class

on pg_constraint.conrelid = pg_class.oid

inner join pg_attribute

on (pg_attribute.attrelid = pg_class.oid

and pg_attribute.attnum = pg_constraint.conkey[1])

inner join

pg_namespace

on pg_class.relnamespace=pg_namespace.oid

where pg_constraint.contype = 'p'

and pg_namespace.nspname='dcf_user'

and pg_class.relname='a_rkcj'

) CC ON (AA.TABLE_SCHEMA = CC.TABLE_SCHEMA

AND AA.TABLE_NAME = CC.TABLE_NAME

AND AA.COL_NAME = CC.COLUMN_NAME)

ORDER BY aa.COL_NUM;