PostgreSQL export data dictionary documentation

PostgreSQL export data dictionary documentation

You need to sort out the data dictionary documents of the current database on the project. The project is not standardized. Such documents should be supplemented later. Each field in these tables is written to a Word document with a huge header. Even if I wrote an SQL statement to query the table structure, it still seems a little troublesome to sort it into word. I have previously written an html document for Oracle to directly generate table structures, so I want to get another postgresql version. I checked some documents and found that pg9.4 does not support file writing. Give up. Finally, this solution is selected to use the message printing function in SQL scripts. Print the generated html document and copy the code of the html document to the text file to save it. Although it is more difficult than oracle, you can finally get the desired html document.

Slq script:

-- 1.0
--
Postgresql-9.4.5
-- Print the data dictionary html
-- After the execution is completed, copy the printed content to the text file in the pgAdmin message window and replace the redundant output: [PGSCRIPT], delete the [QUERY] in the header and the printed QUERY statement,
--The final file is a. html file.
-- Open the saved webpage in a browser and copy the content of the webpage to the Word document. You can organize the content in the following format.
-- Note:
-- The script contains the data dictionary of the verbose and lite versions. When using the data dictionary, switch to the corresponding title.
-- '<Tr> <td> column name </td> <td> type </td> <td> length </td> <td> primary key constraint </td> <td> unique constraint </td> <td> foreign key constraint </td> <td> can it be blank </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
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 (details): column name type length primary key constraint unique constraint foreign key constraint can be empty 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 =. 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 =. 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 =. 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 (Lite 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
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 documents
Print '<! DOCTYPE html> ';
Print 'Print '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 <lines (@ table)
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> primary key constraint </td> <td> unique constraint </td> <td> foreign key constraint </td> <td> can it be blank </td> <td> description </td> </tr>';
-- Print '<tr> <td> column name </td> <td> type </td> <td> description </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

 

-- Appendix:
/*
-- Data dictionary -- details
Select
(Select relname | '--' | (select description from pg_description where objoid = oid and objsubid = 0) from pg_class where oid = a. attrelid) as table name,
A. attname as column name,
Format_type (a. atttypid, a. atttypmod) as type,
(Case when atttypmod-4> 0 then atttypmod-4 else 0 end) as length,
(Case when (select count (*) from pg_constraint where conrelid =. attrelid and conkey [1] = attnum and contype = 'P')> 0 then 'y' else 'n' end) as primary key constraint,
(Case when (select count (*) from pg_constraint where conrelid =. attrelid and conkey [1] = attnum and contype = 'U')> 0 then 'y' else 'n' end) as unique constraint,
(Case when (select count (*) from pg_constraint where conrelid =. attrelid and conkey [1] = attnum and contype = 'F')> 0 then 'y' else 'n' end) as foreign key constraint,
(Case when a. attnotnull = true then 'y' else 'n' end) Can the as be empty,
Col_description (a. attrelid, a. attnum) as description
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 -- Lite version
Select
(Select relname from pg_class where oid = a. attrelid) as table_name,
(Select description from pg_description where objoid = oid and objsubid = 0) | 'table' | '(' | relname | ') 'from pg_class where oid =. attrelid) as table name,
A. attname as column name,
Format_type (a. atttypid, a. atttypmod) as type,
Col_description (a. attrelid, a. attnum) as description
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;
*/

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: