pgsql查看表和列的说明

列说明

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 pn.nspname not in ('pg_catalog','information_schema') -- select pn.oid, pn.* from pg_namespace pn where 1=1
       and pn.nspname not like 'pg_toast%'
       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 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为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||''';' as column_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.nspname,t0.relname, t0.attnum;

表说明

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 pn.nspname not in ('pg_catalog','information_schema') -- select pn.oid, pn.* from pg_namespace pn where 1=1
        and pn.nspname not like 'pg_toast%'
        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 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为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||''';' as table_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.nspname,t0.relname;