之前要将数据库的表结构给做成markdow来写开发设计文档或是接口文档,去找各种开源工具、组件。
整理了一个SQL语句可以查询出表结构,样式如下
SQL语句,里面的jiahui表示数据库的schema,默认是public
SELECT CASE
WHEN t.attnum = -2 THEN
NULL
WHEN t.attnum = -1 THEN
'表名'
ELSE
t.relname
END 表名,
t.attname 字段名,
t.atttype 字段类型或表约束,
t.isnotnull 是否可为空,
t.attrdef 默认值,
t.description 注释
FROM (SELECT b.relname,
a.attnum,
a.attname,
format_type(a.atttypid, a.atttypmod) atttype,
CASE
WHEN a.attnotnull = TRUE THEN
'不可空'
ELSE
'可空'
END isnotnull,
(SELECT
pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
FROM pg_attrdef d
WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum
AND a.atthasdef) attrdef,
c.description
FROM pg_attribute a
JOIN pg_class b
ON a.attrelid = b.oid
LEFT JOIN pg_description c
ON a.attrelid = c.objoid
AND c.objsubid = a.attnum
WHERE a.attnum > 0
AND a.attisdropped = 'f'
AND b.relnamespace = 'jiahui'::regnamespace
AND b.relkind = 'r'
AND NOT a.attisdropped
UNION ALL
SELECT b.relname,
-2,
NULL,
NULL,
NULL,
NULL,
NULL
FROM pg_class b
WHERE b.relnamespace = 'jiahui'::regnamespace
AND b.relkind = 'r'
UNION ALL
SELECT b.relname,
-1,
'字段名',
'字段类型或表约束',
'是否可为空',
'默认值',
'注释'
FROM pg_class b
WHERE b.relnamespace = 'jiahui'::regnamespace
AND b.relkind = 'r'
UNION ALL
SELECT b.relname,
0,
NULL,
string_agg(CASE
WHEN contype = 'p' THEN
'主键约束'
WHEN contype = 'c' THEN
'检查约束'
WHEN contype = 'f' THEN
'外键约束'
WHEN contype = 'u' THEN
'唯一约束'
WHEN contype = 't' THEN
'约束触发器'
WHEN contype = 'x' THEN
'排除约束'
END || ':' || conname || ' ' ||
pg_get_constraintdef(a.oid, 't'),
';'),
NULL,
NULL,
(SELECT description
FROM pg_description
WHERE a.conrelid = objoid
AND objsubid = 0)
FROM pg_constraint a
JOIN pg_class b
ON a.conrelid = b.oid
WHERE a.connamespace = 'jiahui'::regnamespace
AND b.relnamespace = 'jiahui'::regnamespace
AND b.relkind = 'r'
GROUP BY conrelid,
b.relname) t
ORDER BY t.relname,
t.attnum offset 1;