PostgreSQL中的SQL访问注释 [英] SQL access comments in postgreSQL

问题描述

我一直在尝试找到一种 SQL 方法来检索我的方案和PostgreSQL中其他数据库对象的注释。

I have been trying to find a SQL approach to retrieve comments of my schemas and other database objects in PostgreSQL.

I已经在堆栈溢出中看到以下问题：
如何检索PostgreSQL数据库的注释？，其中显示了如何使用以下代码获取数据库的注释：

I have seen the following questions in stackoverflow: How to retrieve the comment of a PostgreSQL database? which shows how to fetch comments for databases, with the following code:

SELECT * FROM pg_shdescription JOIN pg_database ON objoid =
pg_database.oid;

在PostgreSQL中获取表注释列表
，其中显示了如何获取表注释，命令为 SELECT obj_description（'myschema。 mytable':: regclass）; 表示架构中的特定表。

Getting list of table comments in PostgreSQL which shows how to get comments for tables, the command is SELECT obj_description('myschema.mytable'::regclass); for a specific table in a schema.

从PostgreSQL数据库中获取注释，其中显示了如何获取数据库中所有列的注释，命令为：

Retrieving Comments from a PostgreSQL DB which shows how to fetch the comments for all the columns in the database, the command is :

SELECT c.table_schema,c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables AS st
INNER JOIN pg_catalog.pg_description pgd ON (pgd.objoid=st.relid)
INNER JOIN information_schema.columns c ON (pgd.objsubid=c.ordinal_position
AND c.table_schema=st.schemaname AND c.table_name=st.relname);

这些评估从数据库，表和列中获取注释的问题，但它们没有给出

These assess the question of fetching comments from a database, tables and columns, but they do not give the answer for other database objects such as schemas.

我看过 obj_description（object_oid，catalog_name），但无法使其在架构，视图等中正常工作。

I have taken a look at obj_description(object_oid, catalog_name) but did not manage to make it work... for schemas, views, etc.

（我主要对模式感兴趣，但由于其他开发人员可能会对其他情况感兴趣，因此我希望将所有解决方案放在一个地方）。

(I am mainly interested in schemas, but since there are other situations that might interest other developers, I would like to have all the solutions in one place).

有任何线索吗？

编辑：找到了一种从 schemas 获得评论的方法...似乎
有点复杂，可能会更简单。
这是代码：

found a way to get the comments from schemas... Seems a bit complicated, could be simplier. Here is the code:

SELECT

CASE 
    WHEN nspname LIKE E'pg\_temp\_%' THEN 1 
    WHEN (nspname LIKE E'pg\_%') THEN 0  
    ELSE 3 
END AS nsptyp, nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) 
    AS namespaceowner, 
    nspacl, description,  
    has_schema_privilege(nsp.oid, 'CREATE') as cancreate 
FROM pg_namespace nsp 
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid  
WHERE NOT ((nspname = 'pg_catalog' AND EXISTS 
(SELECT 1 FROM pg_class 
    WHERE relname = 'pg_class' 
    AND relnamespace = nsp.oid LIMIT 1)) OR  
(nspname = 'information_schema' AND 
    EXISTS (SELECT 1 FROM pg_class 
            WHERE relname = 'tables' 
            AND relnamespace = nsp.oid LIMIT 1)) OR  
(nspname LIKE '_%' AND 
    EXISTS (SELECT 1 FROM pg_proc 
            WHERE proname='slonyversion' 
            AND pronamespace = nsp.oid LIMIT 1)) OR  
(nspname = 'dbo' AND 
    EXISTS (SELECT 1 FROM pg_class 
            WHERE relname = 'systables' 
            AND relnamespace = nsp.oid LIMIT 1)) OR  
(nspname = 'sys' AND 
    EXISTS (SELECT 1 FROM pg_class 
            WHERE relname = 'all_tables' 
            AND relnamespace = nsp.oid LIMIT 1))
) 
AND nspname NOT LIKE E'pg\_temp\_%'
AND nspname NOT LIKE E'pg\_toast_temp\_%' 
ORDER BY 1, nspname

由于原始代码很长，我喜欢简洁的解决方案，所以这里有一些更好的方法：

As the original code is quite lengthy and I like concise solutions, here is something better:

SELECT * FROM pg_namespace AS nsp LEFT OUTER JOIN pg_description AS des ON des.objoid=nsp.oid;

长代码基本上从PostgreSQL中排除了一堆模式（尽管我不知道为什么不这样做）

The long code basically excludes a bunch of schemas from PostgreSQL (though I do not know why it doesn't hide pg_toast on my machine).

推荐答案

好，经过大量研究，我得出的结论是：使用以下代码获取PostgreSQL中对象的描述：

Ok, after a lot of research, I have come to the conclusion that one can use the following code to get descriptions of objects in PostgreSQL:

SELECT * FROM [pg_catalog_table] AS nsp LEFT OUTER JOIN pg_description AS des ON des.objoid=nsp.oid;

[pg_catalog_table]应该用pg_catalog中包含OID列的表代替...例如（但不限于）：
pg_type; pg_namespace;

The [pg_catalog_table] should be substituted by a table in the pg_catalog which contains an OID column... For instance (but not limited): pg_type ; pg_namespace ; etc.

并使用此代码

SELECT * FROM pg_database AS nsp  LEFT OUTER JOIN pg_shdescription AS sdes ON sdes.objoid=nsp.oid;

获取来自数据库的描述。

for descriptions from databases.

视图在pg_class中列出了一些技巧，因为它们在pg_class中也列出了其余对象（此处还有更多工作要做）。

For views is a bit more tricky as they are listed in pg_class which also lists the rest of the objects (more work to do here).

似乎没有服务器的注释。

There doesn't seem to be comments for servers.

这篇关于PostgreSQL中的SQL访问注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！