【PostgreSql】生成数据字典

pg查询元数据,生成表数据字典:

 

效果:

 

  

代码:

SELECT
    A .attname 字段,   
    concat_ws (
        '',
        T .typname,
        SUBSTRING (
            format_type (A .atttypid, A .atttypmod)
            FROM
                '\(.*\)'
        )
    ) AS 类型,
        case when s.pk is not null then ''
                               else ''
    end as 主键,
    case A.attnotnull when 'f' then ''
                      when 't' then ''
    end as 空,
    d.description 注释
FROM pg_attribute A
INNER JOIN pg_class C on A .attrelid = C .oid
INNER JOIN pg_type T on A .atttypid = T .oid
LEFT JOIN (SELECT conrelid, unnest(conkey) as pk
                        FROM pg_constraint
                        WHERE contype = 'p') S ON S.conrelid = C .oid
                                   AND A.attnum = S.pk
LEFT JOIN pg_description d on d.objoid = A .attrelid
                                                    AND d.objsubid = A .attnum
LEFT JOIN pg_namespace n on n.oid = c.relnamespace    
WHERE A.attnum > 0
AND n.nspname = 'public'   
AND C.relname = 't_gfecp_bigdata_blacklist'
ORDER BY
    C .relname,
    A .attnum

 

posted @ 2020-05-06 18:48  TurboWay  阅读(2021)  评论(0编辑  收藏  举报