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;
列说明with tmp_tab as ( select pc.oid as ooid ,pn.nspname ,pc.* from pg_class pcleft outer join pg_namespace pn on pc.relnamespace = pn.oid where 1=1 and pc.relkind in ('r') and pn.nspname not in ('