[PostgreSQL] CREATE TABEL + COMMENT 한방 생성 script

본문에서 소개하는 쿼리 한방으로
CREATE TABLE 및 COMMENT 등록 쿼리를 한방에 생성 할 수 있다.
단, DBMS상에 이미 잘 만들어진 테이블과 잘 등록된 COMMENT 가 있어야 한다.

이미 잘 만들어진 테이블과 COMMENT 가 있는데, 본문의 쿼리가 왜 필요하냐? 반문하실 수 있다.
사용중인 DBMS가 아닌 다른 곳으로 이사를 가야한다던지,
갑자기
뜬금없이
느닷없이
불현듯
고객이 ERD를 요청할 수 있다.
그럴때 스크립트라도 있다면 ERD 생성이 보다 수월하지 않을까?
좌우간 있으면 쓸일이 있다.

사용방법

 

1. CTRL + A (모두 선택)

 

2. CTRL + ENTER (쿼리 실행)(DBeaver)

 

3. 바인딩 변수에 테이블명 입력

 

4. ddl_sql 부분 선택 CTRL + C

 

5. Notepadd++ 등에 CTRL + V 확인

 

쿼리구성

1 테이블 생성 스크립트

2 테이블 주석 생성 스크립트

3 컬럼 주석 생성 스크립트

 

전체쿼리

/*
 * DDL 생성
 */
/* 테이블 생성 스크립트*/
SELECT *
FROM   (
        SELECT tab_name AS tb
             , 1 AS ord
             , 'CREATE TABLE ' || tab_name || ' (' || CHR(13) || 
               STRING_AGG( '    ' ||
                   RPAD(col_name,30,' ') || ' ' || RPAD(data_type,30,' ') ||
                   CASE WHEN aa.attnotnull = true THEN ' NOT NULL' ELSE ' NULL' END
                   , ',' || CHR(13)
               ) ||
               COALESCE(
                   (SELECT E'\n, ' || 'CONSTRAINT' || ' ' || conname || ' ' || pg_get_constraintdef(oid) 
                    FROM   pg_constraint
                    WHERE  connamespace::regnamespace::varchar = aa.nspname
                    AND    conrelid::regclass::varchar = aa.tab_name
                    AND    contype = 'p')
                    , '' ) ||
               (SELECT COALESCE(STRING_AGG(E'\n, ' || 'CONSTRAINT' || ' ' || conname || ' ' || 
                       pg_get_constraintdef(oid), CHR(13)),'') as fk
                FROM   pg_constraint
                WHERE  1 = 1
                AND    connamespace::regnamespace::varchar = aa.nspname
                AND    conrelid::regclass::varchar         = aa.tab_name
                AND    contype = 'f') ||
               CHR(13) || ');' AS ddl_sql
        FROM   (
                SELECT cc.*
                     , CASE WHEN data_type = 'numeric' THEN 
                           CASE WHEN numeric_scale > 0 THEN data_type || '(' || numeric_precision::varchar ||
                                     ','||numeric_scale::varchar || ')' 
                           ELSE data_type || '(' || numeric_precision::varchar || ')' END
                         WHEN data_type = 'timestamp without time zone' THEN 'timestamp'
                       ELSE CASE WHEN character_maximum_length > 0 THEN 
                                 replace(data_type,'character varying','varchar') || '(' || 
                                 character_maximum_length::varchar || ')'
                            ELSE data_type END
                       END AS data_type
                FROM   (SELECT n.nspname 
                             , c.relname              AS tab_name
                             , obj_description(c.oid) AS tab_cmt
                             , a.attnum               AS col_seq
                             , a.attname              AS col_name
                             , a.attrelid
                             , a.attnotnull
                             , col_description(a.attrelid, a.attnum) AS col_cmt
                        FROM   pg_catalog.pg_class     c
                             , pg_catalog.pg_namespace n
                             , pg_catalog.pg_attribute a
                        WHERE  c.relnamespace = n.oid
                        AND    a.attrelid = c.oid
                        AND    c.relkind  = 'r'
                        AND    c.relname ilike '${BaindingValue}'
                        AND    a.attnum  > 0
                        AND    a.attisdropped IS FALSE
                        AND    pg_catalog.pg_table_is_visible(c.oid)
                       ) AS cc
                     , information_schema.columns s
                WHERE  s.table_schema = cc.nspname
                AND    s.table_name   = cc.tab_name
                AND    s.column_name  = cc.col_name
                ORDER BY nspname, tab_name, col_seq
               ) aa
        WHERE  1 = 1
        GROUP  BY nspname, tab_name

        UNION ALL

        /* 테이블 주석 생성 스크립트 */
        SELECT C.RELNAME AS tb
             , 2 AS ord
             , 'COMMENT ON TABLE  ' ||  RPAD(C.RELNAME, 58, ' ') || ' IS ''' || 
               REPLACE(OBJ_DESCRIPTION(C.OID) , '''', '''''' ) || ''';' AS ddl_sql
        FROM   PG_CATALOG.PG_CLASS C INNER JOIN PG_CATALOG.PG_NAMESPACE N ON C.RELNAMESPACE=N.OID 
        WHERE  C.RELKIND = 'r'
        AND    relname ILIKE '${BaindingValue}'

        union all

        /* 컬럼 주석 생성 스크립트 */
        SELECT ps.relname AS tb
             , 3 AS ord
             , 'COMMENT ON COLUMN '|| ps.relname || '.' || RPAD(pa.attname, 30, ' ') ||' IS ''' || 
               REPLACE((SELECT description FROM PG_DESCRIPTION 
                        WHERE OBJSUBID <> 0 AND objoid=pa.attrelid AND objsubid=pa.attnum), '''', '''''' ) ||
                        ''';' AS ddl_sql
        FROM   pg_stat_all_tables ps
             , pg_attribute       pa
             , information_schema.columns ic
        WHERE  1=1
        AND    PS.RELID=PA.ATTRELID
        AND    ps.relname ILIKE '${BaindingValue}'
        AND    ps.schemaname = ic.table_schema
        AND    ps.relname    = ic.table_name
        AND    pa.attname    = ic.column_name

)
WHERE 1=1
ORDER BY tb, ord