pg 生成数据字典

复制代码select (select relname||'--'||(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where oid=a.attrelid) as table_name, a.attname as column_name, format_type(a.atttypid,a.atttypmod) as data_type, (case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束, (case when a.attnotnull=true then 'Y' else 'N' end) as nullable, col_description(a.attrelid,a.attnum) as comment from pg_attribute a where attstattarget=-1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind ='r' and relname like 'tbl_%')) order by table_name,a.attnum;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select

(select relname||'--'||(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where oid=a.attrelid) as table_name,

a.attname as column_name,

format_type(a.atttypid,a.atttypmod) as data_type,

(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,

(case when a.attnotnull=true then 'Y' else 'N' end) as nullable,

col_description(a.attrelid,a.attnum) as comment

from pg_attribute a

where attstattarget=-1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind ='r' and relname like 'tbl_%'))

order by table_name,a.attnum;