想实现一个通用的元数据，从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
