获取postgresql数据字典

SELECT
pc.relname AS tableName,
    pa.attname AS columnName,
    pa.attnum AS columnorder,
    pt.typname AS columnType,
    concat_ws(
        '',
        pt.typname,
        SUBSTRING(
            format_type(pa.atttypid, pa.atttypmod) from '\(.*\)'
        )
    ) as 列类型, (CASE WHEN pa.attlen > 0 THEN pa.attlen ELSE pa.atttypmod -
        4 END) AS columnLength,
    pa.attnotnull AS isNullAble, (CASE WHEN(SELECT COUNT( * ) FROM pg_constraint WHERE conrelid =
        pa.attrelid AND conkey[1] = attnum AND contype = 'p') > 0 THEN TRUE ELSE FALSE END) AS isPrimary,
    pd.description AS columnDescription,
    CASE atttypid
      WHEN 21 /*int2*/ THEN 16
      WHEN 23 /*int4*/ THEN 32
      WHEN 20 /*int8*/ THEN 64
      WHEN 1700 /*numeric*/ THEN
    CASE WHEN atttypmod = -1
      THEN null
      ELSE((atttypmod - 4) >> 16) & 65535--calculate the precision
      END
      WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
      WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
      ELSE null
      END AS numeric_precision,
    CASE
      WHEN atttypid IN(21, 23, 20) THEN 0
      WHEN atttypid IN(1700) THEN
      CASE
      WHEN atttypmod = -1 THEN null
      ELSE(atttypmod - 4) & 65535--calculate the scale
      END
      ELSE null
      END AS numeric_scale
FROM
  pg_class pc,
  pg_attribute pa,
  pg_type pt,
  pg_description pd
WHERE pc.oid = pa.attrelid
  AND pt.oid = pa.atttypid
  AND pd.objoid = pa.attrelid
  AND pd.objsubid = pa.attnum
  --AND pc.relname = 'table_name'
ORDER BY pc.relname DESC,    pa.attnum ASC