Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PostgreSQL export data dictionary documents     - Linux distributed message queue RocketMQ deployment and monitoring - Dual Master (Server)

- Ansible installation configuration and simple to use (Server)

- MongoDB relations, references, index query coverage (Database)

- Linux for enterprises to build a firewall (Linux)

- Linux system boot process ARM platforms (Linux)

- Linux using RAID how to use Mdadm Tool Management Software RAID (Linux)

- CentOS 6.x and CentOS7 installation RPMforge (Linux)

- MongoDB in bulk timestamp change the date format (Database)

- Linux System Getting Started Learning: Repair (Linux)

- How to properly set up a Linux swap partition (Linux)

- Linux SU command security Suggestions (Linux)

- Linux server security - the web server configuration (Linux)

- How to run Docker client in Windows operating system (Linux)

- ImageMagick Tutorial: How to cut images in Linux command line (Linux)

- Bash job control (Linux)

- How to use static, class, abstract method in Python (Programming)

- Android engineers interview questions (Programming)

- Why not use the ifconfig command under RedHat Linux 5 (Linux)

- Binder began to talk about inter-process communication mechanism from Android AIDL (Programming)

- Experience RHEL7 new features (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:      
 
- JBPM6 Tutorial - Fast Fun JBPM table (Linux)
- Linux shell script under the use of randomly generated passwords (Programming)
- Installation Enpass secure password manager on Ubuntu (Linux)
- Linux basic articles of the boot process (Linux)
- CentOS yum source configuration (Linux)
- Android Studio Clear Project or Rebuild Project Error (Linux)
- How to Install Apache on Linux servers (Server)
- Elaborate 10-point difference between the new and malloc (Programming)
- CentOS 6.5 system installation Oracle11.2.0.4 basic steps (Database)
- Server Security Analysis attack on Linux (Linux)
- Linux vi command list (Linux)
- Ubuntu How to install screen recording tool Simple Screen Recorder 0.3.1 (Linux)
- To install MySQL on Linux (Database)
- Command-line based tools for better alternatives (Linux)
- CentOS6.6 ordinary users to use sudo command to borrow root user privileges (Linux)
- Virt Related Command Summary (Linux)
- C ++ stderr / stdout redirected to a file (Programming)
- Java static internal class (Programming)
- Linux System Getting Started Learning: Using the Linux command line detected DVD burner name and write speeds (Linux)
- Linux System Getting Started Learning: The Linux logrotate (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.