| |
We need to tidy up the current data dictionary database documentation on the project. The project is not standardized, so long as the latter part of this document to fill. So many tables, each field in a document written word really big heart. Even wrote a lookup table in front of the structure sql, but the final word in order to still feel a bit of trouble. Oracle previously written a document directly generate html table structure, so now also want to get hold of postgresql version. Check a lot of documents, I found pg9.4 does not support writing files. Reluctantly give up. And finally chose one such program, use sql script Print message function. The html documentation generated print out a copy of the final document html code into a text file saved, although the trouble spots than oracle, finally can get html files want.
slq script:
--1.0
--2015-11-30
--postgresql-9.4.5
- Print out the data dictionary html
- Finished in pgAdmin message window, to print the contents are copied to a text file, replace the excess output: [PGSCRIPT], remove the head of the [QUERY] and print out the query,
- Finally, the file is saved as .html files.
- Open the saved web browser, and then copy the contents of the page into a word document, the following order form can be
--note:
- Script contains detailed version and simple version of the two versions of the data dictionary, use the time to pay attention to switch to the corresponding title
- '< tr > < td > column name < / td > < td > Type < / td > < td > length < / td > < td > a primary key constraint < / td > < td > The only constraint < / td > < td> foreign key constraint < / td > < td > nullability < / td > < td > description < / td > < / tr > ';
- '< tr > < td > column name < / td > < td > Type < / td > < td > Description < / td > < / tr >';
begin
- Query table name
set @table = select distinct relname, relname || '(' || (select description from pg_description where objoid = oid and objsubid = 0) || 'table' || ')' 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;
- Data Dictionary (detailed): Column Name Type Length primary key constraint unique constraint foreign key constraint nullability description
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;
/ *
- Data Dictionary (simple version): Column Name Type Description
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;
* /
- Print html document
print '< ! DOCTYPE html >';
print '< html >';
print '< head >';
print '< meta http-equiv = "Content-Type" content = "text / html; charset = utf-8" / >';
print '< title > data dictionary < / 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
begin
set @table_name = @table [@i] [0];
print @table [@i] [1];
print '< table >';
print '< tr > < td > column name < / td > < td > Type < / td > < td > length < / td > < td > a primary key constraint < / td > < td > The only constraint < / td > < td > foreign key constraint < / td > < td > nullability < / td > < td > description < / td > < / tr > ';
--print '< tr > < td > column name < / td > < td > Type < / td > < td > Description < / td > < / tr >';
set @ j = 0;
while @j
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
- Attachment:
/ *
- Data Dictionary - Edition Details
select
(Select relname || '-' || (select description from pg_description where objoid = oid and objsubid = 0) from pg_class where oid = a.attrelid) as the table name,
a.attname as column name,
format_type (a.atttypid, a.atttypmod) as the type,
(Case when atttypmod-4> 0 then atttypmod-4 else 0 end) as 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 the primary key constraint,
(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 a unique constraint,
(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 a foreign key constraint,
(Case when a.attnotnull = true then 'Y' else 'N' end) as nullability,
col_description (a.attrelid, a.attnum) as described
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;
- Data Dictionary - short version
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) || 'table' || '(' || relname || ')' from pg_class where oid = a.attrelid) as the table name,
a.attname as column name,
format_type (a.atttypid, a.atttypmod) as the type,
col_description (a.attrelid, a.attnum) as described
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;
* / |
|
|
|