人大金仓表结构查询
修改以下“模式名称”为要查询的模式

SELECT x.relname       AS 表英文名,
       bn.table_comment AS 表中文名,
       x.attnum,
       x.字段名称,
       x.字段类型,
       x.字段长度,
       x.字段说明
FROM (
    SELECT c.relname,
           a.attnum,
           a.attname AS 字段名称,
           CASE
               WHEN t.typname = 'int4' THEN 'int'
               ELSE t.typname
           END AS 字段类型,
           CASE
               WHEN a.atttypmod > 0 THEN a.atttypmod - 4
               ELSE NULL
           END AS 字段长度,
           b.description AS 字段说明
    FROM pg_class c
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_type t ON a.atttypid = t.oid
    LEFT JOIN pg_description b ON a.attrelid = b.objoid AND a.attnum = b.objsubid
    WHERE c.relkind = 'r'
      AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '模式名称')
      AND a.attnum > 0
) AS x
LEFT JOIN (
    SELECT c.relname AS table_name,
           d.description AS table_comment
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
    WHERE c.relkind = 'r'
      AND n.nspname = '模式名称'
) bn ON x.relname = bn.table_name
ORDER BY x.relname, x.attnum;