如何检索 PostgreSQL 数据库的注释？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11493978/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to retrieve the comment of a PostgreSQL database?
提问by C2H5OH
I recently discovered you can attach a commentto all sort of objects in PostgreSQL. In particular, I'm interested on playing with the comment of a database. For example, to setthe comment of a database:
我最近发现您可以在 PostgreSQL 中为所有类型的对象附加注释。特别是，我对处理数据库的评论很感兴趣。例如，要设置数据库的注释：
COMMENT ON DATABASE mydatabase IS 'DB Comment';
However, what is the opposite statement, to getthe comment of mydatabase?
但是，相反的语句是什么，以获得的评论mydatabase？
From the psqlcommand line, I can see the comment along with other information as a result of the \l+command; which I could use with the aid of awk in order to achieve my goal. But I'd rather use an SQL statement, if possible.
从psql命令行中，我可以看到其他资料的结果一起注释\l+命令; 我可以在 awk 的帮助下使用它来实现我的目标。但如果可能的话，我宁愿使用 SQL 语句。
采纳答案by martin
To get the comment on the database, use the following query:
要获取对数据库的评论，请使用以下查询：
select description from pg_shdescription
join pg_database on objoid = pg_database.oid
where datname = '<database name>'
This query will get you table comment for the given table name:
此查询将为您提供给定表名的表注释：
select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
where relname = '<your table name>'
If you use the same table name in different schemas, you need to modify it a bit:
如果在不同的schema中使用相同的表名，则需要稍微修改一下：
select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
join pg_namespace on pg_class.relnamespace = pg_namespace.oid
where relname = '<table name>' and nspname='<schema name>'
回答by Erwin Brandstetter
First off, your query for table comments can be simplified using a cast to the appropriate object identifier type:
首先，可以使用转换为适当的对象标识符类型来简化对表注释的查询：
SELECT description
FROM pg_description
WHERE objoid = 'myschema.mytbl'::regclass;
The schema part is optional. If you omit it, your current search_pathdecides visibility of any table named mytbl.
架构部分是可选的。如果您省略它，您的当前search_path决定任何名为 的表的可见性mytbl。
Better yet, there are dedicated functionsin PostgreSQL to simplify and canonize these queries. The manual:
更好的是，PostgreSQL 中有专门的函数来简化和规范这些查询。手册：
obj_description(object_oid,catalog_name)... get comment for a database object
shobj_description(object_oid,catalog_name)... get comment for a shared database object
obj_description(object_oid, ... 获取数据库对象的注释catalog_name)
shobj_description(object_oid, ... 获取共享数据库对象的注释catalog_name)
Description for table:
表说明：
SELECT obj_description('myschema.mytbl'::regclass, 'pg_class');
Description for database:
数据库说明：
SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS "Description"
FROM pg_catalog.pg_database d
WHERE datname = 'mydb';
How do you find out about that?
你怎么知道的？
Well, reading the excellent manual is enlightening. :)
But there is a more direct route in this case: most psqlmeta commands are implemented with plain SQL. Start a session with psql -E, to see the magic behind the curtains. The manual:
好吧，阅读优秀的手册很有启发性。:)
但是在这种情况下有一个更直接的途径：大多数psql元命令都是用普通的 SQL 实现的。开始会话，以了解窗帘背后的魔力。手册：psql -E
-E--echo-hiddenEcho the actual queries generated by
\dand other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variableECHO_HIDDENtoon.
-E--echo-hidden回显由
\d和其他反斜杠命令生成的实际查询。你可以用这个来研究psql的内部操作。这相当于将变量设置ECHO_HIDDEN为on。
回答by Geoff Hansen
For tables, try
对于表，请尝试
\dd TABLENAME
This shows the comment I added to a table
这显示了我添加到表格中的评论
回答by Mukhammadsher
This query will return the comment of a table
此查询将返回表的注释
SELECT obj_description('public.myTable'::regclass)
FROM pg_class
WHERE relkind = 'r' limit 1
回答by user3732995
This query will get only table comment for all tables
此查询将仅获取所有表的表注释
SELECT RelName,Description
FROM pg_Description
JOIN pg_Class
ON pg_Description.ObjOID = pg_Class.OID
WHERE ObjSubID = 0
