postgresql 系统表的使用(兼容oracle系统表)
2021-06-16 14:54:27    10    0    0
ivan

    1、查询 Table的字段、字段注释以及所属的模式(兼容oracle user_col_comments)

--create view user_col_comments as --可改造成视图来兼容 user_col_comments

select pn.nspname, pc.relname, pa.attname, pd.description
from pg_class pc
join pg_namespace pn on pc.relnamespace = pn.oid
JOIN pg_attribute pa ON pc.oid = pa.attrelid
left join pg_description pd on pd.objoid = pc.oid and pa.attnum = pd.objsubid
where pc.relkind='r' and pa.attnum > 0
and pc.relnamespace = ( select oid from pg_namespace where nspname = 'schema_name' ) --schema_name 所属模式名
-- and pc.relname = 'table_name'; --table_name 表名,select可单独查询一张表的

2、查询 Table、注释以及所属的模式(兼容oracle user_tab_comments)

select n.nspname schema_name,c.relname table_name,
pg_catalog.obj_description(c.oid, 'pg_class') as comments
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r';

Pre: Debian系dash改bash

Next: postgresql docs

10
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content