postgresql 导出数据字典文档

16-01-12 来源：[db:作者]

收藏我要投稿

项目上需要整理目前数据库的数据字典文档。项目不规范，这种文档只要后期来补。这么多张表，每个字段都写到word文档里真心头大。就算前面写了个查询表结构的sql，但是最后整理到word里还是感觉有点麻烦。以前写过一个oracle直接生成表结构的html文档，所以现在也想再弄个postgresql 版本的。查了一番文档，发现pg9.4不支持写文件。无奈放弃。最后选了一个这种方案，利用sql脚本中打印消息的功能。把生成的html文档打印出来，最后拷贝html文档代码到文本文件中保存，虽然比oracle那个麻烦了点，总算能得到想要的html文档了。

slq脚本：

--1.0 
--2015-11-30
--postgresql-9.4.5
--打印出数据字典html
--执行完毕，在pgAdmin的消息窗口，把打印内容拷贝到文本文件中，替换掉多余的输出：[PGSCRIPT ] ，删除头部的[QUERY    ]及打印出的查询语句，
--最后把文件另存为.html文件。
--用浏览器打开保存的网页，然后拷贝页面内容到word文档中，下面整理格式就可以了
--注意：
--脚本里包含了详细版，和简版两个版本的数据字典，使用的时候注意切换到对应的标题
--'<tr><td>列名</td><td>类型</td><td>长度</td><td>主键约束</td><td>唯一约束</td><td>外键约束</td><td>可否为空</td><td>描述</td></tr>';
--'<tr><td>列名</td><td>类型</td><td>描述</td></tr>';
begin
    --查询表名
    set @table = select distinct relname, relname||'('||(select description from pg_description where objoid = oid and objsubid = 0) ||'表'||')' as table_name 
                from pg_class c,pg_attribute a 
                where c.oid=a.attrelid 
                and attstattarget=-1 
                and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))
                order by table_name;
    --数据字典(详细版)：列名 类型 长度 主键约束 唯一约束 外键约束 可否为空 描述        
    set @att = select (select relname from pg_class where oid=a.attrelid) as table_name,
            '<tr><td>'||a.attname||'</td>'
            ||'<td>'||format_type(a.atttypid,a.atttypmod)||'</td>'
            ||'<td>'||(case when atttypmod-4>0 then atttypmod-4 else 0 end)||'</td>'
            ||'<td>'||(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end)||'</td>'
            ||'<td>'||(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end)||'</td>'
            ||'<td>'||(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end)||'</td>'
            ||'<td>'||(case when a.attnotnull=true then 'Y' else 'N' end)||'</td>'
            ||'<td>'||col_description(a.attrelid,a.attnum)||'</td></tr>'
            from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))
            order by table_name,attnum;
/*
    --数据字典(简版)：列名 类型 描述
    set @att = select (select relname from pg_class where oid=a.attrelid) as table_name,
            '<tr><td>'||a.attname||'</td>'
            ||'<td>'||format_type(a.atttypid,a.atttypmod)||'</td>'
            ||'<td>'||col_description(a.attrelid,a.attnum)||'</td></tr>'
            from pg_attribute a 
            where attstattarget=-1 
            and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))
            order by table_name,attnum;
    */
    --打印html文档
    print '<!DOCTYPE html>';
    print '<html>';
    print '<head>';
    print '<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />';
    print '<title>数据字典</title>';
    print '<style type="text/css">';
    print 'table { border-collapse: collapse; border-spacing: 0;}';
    print 'table td {border: solid 1px #000;}';
    print '</style>';

    set @i=0;
    while @i < lines(@table)
    begin
        set @table_name = @table[@i][0];
        print @table[@i][1];
        print '<table>';
        print '<tr><td>列名</td><td>类型</td><td>长度</td><td>主键约束</td><td>唯一约束</td><td>外键约束</td><td>可否为空</td><td>描述</td></tr>';
        --print '<tr><td>列名</td><td>类型</td><td>描述</td></tr>';
        set @j=0;
        while @j < lines(@att)
        begin
            if @att[@j][0] = @table_name
            begin
            print @att[@j][1];
            end
            set @j=@j+1;
        end
        print '</table>';
        set @i=@i+1;
    end
end



--附：
/*
--数据字典--详细版
select 
(select relname ||'--'||(select description from pg_description where objoid = oid and objsubid = 0) from pg_class where oid=a.attrelid) as 表名,
a.attname as 列名,
format_type(a.atttypid,a.atttypmod) as 类型,
(case when atttypmod-4>0 then atttypmod-4 else 0 end) as 长度,
(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 可否为空,
col_description(a.attrelid,a.attnum) as 描述
from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))
order by 表名,attnum;

--数据字典--简版
select 
(select relname from pg_class where oid=a.attrelid) as table_name,
(select (select description from pg_description where objoid = oid and objsubid = 0) ||'表'||'('||relname ||')' from pg_class where oid=a.attrelid) as 表名,
a.attname as 列名,
format_type(a.atttypid,a.atttypmod) as 类型,
col_description(a.attrelid,a.attnum) as 描述
from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))
order by table_name,attnum;
*/

点击复制链接与好友分享!回本站首页