PostgreSQL 匯出資料字典文檔
項目上需要整理目前資料庫的資料字典文檔。項目不規範，這種文檔只要後期來補。這麼多張表，每個欄位都寫到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;
*/
------------------------------------華麗麗的分割線------------------------------------
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
------------------------------------華麗麗的分割線------------------------------------
PostgreSQL 的詳細介紹：請點這裡
PostgreSQL 的：請點這裡
本文永久更新連結地址：
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
