postgresql 查看表、列的备注信息

最近在整理postgresql数据库的表、列的备注信息时，用到了如下的sql

表的备注

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
      from pg_class pc
           left outer join pg_namespace pn
                        on pc.relnamespace = pn.oid
      where 1=1
       and pc.relkind in ('r')
       and pc.relnamespace = 2200 -- select pn.oid, pn.* from pg_namespace pn where 1=1
       and pc.oid not in (
          select inhrelid
            from pg_inherits
       )
       and pc.relname not like '%peiyb%'
    order by pc.relname
),tmp_desc as (
   select pd.*
     from pg_description pd
    where 1=1
      and pd.objsubid = 0
      --and pd.objoid=168605
)
select t0.*
  from (
        select tab.nspname,
               tab.relname,
               de.description,
               'comment on table '||tab.nspname||'.'||tab.relname||' is '''||de.description||''';'
          from tmp_tab tab
               left outer join tmp_desc de
                            on tab.ooid = de.objoid 
         where 1=1    
        ) t0
 where 1=1
   and t0.description is not null
order by t0.relname   
;

列的备注

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
      from pg_class pc
           left outer join pg_namespace pn
                        on pc.relnamespace = pn.oid
      where 1=1
       and pc.relkind in ('r')
       and pc.relnamespace = 2200 -- select pn.oid, pn.* from pg_namespace pn where 1=1
       and pc.oid not in (
          select inhrelid
            from pg_inherits
       )
       and pc.relname not like '%peiyb%'
    order by pc.relname
),tmp_col as (
   select pa.*
     from pg_attribute pa
    where 1=1
      --and pa.attrelid = 168605
      and pa.attname not in (
      'tableoid',
      'cmax',
      'xmax',
      'cmin',
      'xmin',
      'ctid'
      )
),tmp_desc as (
   select pd.*
     from pg_description pd
    where 1=1
      and pd.objsubid <> 0
      --and pd.objoid=168605
)
select t0.*
  from (
        select tab.nspname,
               tab.relname,
               tc.attname,
               tc.attnum,
               de.description,
               'comment on COLUMN '||tab.nspname||'.'||tab.relname||'.'||tc.attname||' is '''||de.description||''';'
          from tmp_tab tab
               left outer join tmp_col tc
                            on tab.ooid = tc.attrelid
               left outer join tmp_desc de
                            on tc.attrelid = de.objoid and tc.attnum = de.objsubid
       ) t0
 where 1=1
   and t0.description is not null
order by t0.relname, t0.attnum
;

参考:
http://postgres.cn/docs/9.6/catalog-pg-class.html
http://postgres.cn/docs/9.6/catalog-pg-attribute.html
http://postgres.cn/docs/9.6/catalog-pg-description.html