PostgreSQL 导出数据字典文档(2)

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;
*/

------------------------------------华丽丽的分割线------------------------------------

CentOS 6.3环境下yum安装PostgreSQL 9.3

PostgreSQL缓存详述

Windows平台编译 PostgreSQL

Ubuntu下LAPP（Linux+Apache+PostgreSQL+PHP)环境的配置与安装

Ubuntu上的phppgAdmin安装及配置

CentOS平台下安装PostgreSQL9.3

PostgreSQL配置Streaming Replication集群

如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin 

------------------------------------华丽丽的分割线------------------------------------