Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PostgreSQL export data dictionary documents     - Vim plugin installation YouCompleteMe (Linux)

- CentOS yum source deployment (Linux)

- Chrome plug-in management, online-offline installation, part of the plug presentations (Linux)

- Oracle 11g principle study Dataguard (Database)

- Linux Nginx installation and configuration instructions (Server)

- Monitoring network traffic with Iptraf in Linux environment (Linux)

- Linux kernel IPv6 protocol closed manner (Linux)

- Ubuntu 14.04 to install file editor KKEdit 0.1.5 version (Linux)

- 11.2.04 Oracle RAC directory crfclust.bdb file is too large, Bug 20186278 (Database)

- Supervisor Installation and Configuration (Server)

- MariaDB phpMyAdmin installation and configuration issues to resolve under CentOS7 (Database)

- RCU lock in the evolution of the Linux kernel (Linux)

- CentOS 7 x64 compiler installation Tengine 2.0.3 Comments (Server)

- MNIST presentation and database conversion (Database)

- Linux find command usage summary (Linux)

- Ubuntu install the camera driver (Linux)

- Oracle 11g RMAN virtual private directory (Database)

- C ++ 11 feature: decltype keywords (Programming)

- Use smem visual display Linux memory usage (Linux)

- How to force Linux users to change the initial password the first time you log in (Linux)

 
         
  PostgreSQL export data dictionary documents
     
  Add Date : 2018-11-21      
         
         
         
  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;
* /
     
         
         
         
  More:      
 
- Iptables on the request URL for IP access control (Linux)
- High-performance JavaScript loops and flow control (Programming)
- Effective Java - lazy initialization (Programming)
- The lambda expression Java8 (constructor references) (Programming)
- Linux server Php injection prevention (Linux)
- After installing minimize RHEL / CentOS 7 we need to do (Linux)
- Linux configuration Samba server (Server)
- Linux rename command usage in learning to modify the file name (Linux)
- How to recover deleted files in Linux systems (Linux)
- Source install Python3.4 on CentOS (Linux)
- MariaDB 10.1 and MySQL 5.7 in general performance on commodity hardware (Database)
- Hutchison DG standby database CPU consumption reached bottleneck repair (Database)
- Linux and SELinux Exploration Program Manager (Linux)
- Let OS X support NTFS write file (Linux)
- The user of fedora is not in the sudoers file method to solve (Linux)
- HTML5 Application Cache (Programming)
- Automatic and Manual Proxy Settings Switch GNOME Shell Extension Proxy Switcher Recommend (Linux)
- Ubuntu users install the video driver Nvidia Driver 334.21 (Linux)
- The Zabbix2.4.5 source compiler installation under Ubuntu 14.04 (Server)
- Install Java JDK 8 in CentOS 7 / 6.5 / 6.4 (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.