SQL 在 PostgreSQL 中获取表注释列表

Getting list of table comments in PostgreSQL

提问by Bionicman303

Postgresql allows adding comments to objects such as tables. For example I've added a comment to table "mytable" by using this SQL command:

Postgresql 允许向对象（例如表）添加注释。例如，我使用以下 SQL 命令向“mytable”表添加了一条注释：

COMMENT ON TABLE mytable IS 'This is my table.';

My question is: If I want to use a SQL-command to get all tables along with their respective comment - how would I do this? What would be the appropriate query for this?

我的问题是：如果我想使用 SQL 命令来获取所有表及其各自的注释 - 我该怎么做？什么是合适的查询？

Thanks in advance! Cheers!

提前致谢！干杯!

回答by a_horse_with_no_name

All comments are stored in pg_description

所有评论都存储在pg_description

To get the comments on a table, you need to join it to pg_class

要获得对表的评论，您需要将其加入到pg_class

As an alternative you can also use the function obj_description()to retrieve this information:

作为替代方案，您还可以使用该函数obj_description()来检索此信息：

SELECT obj_description(oid)
FROM pg_class
WHERE relkind = 'r'

Edit

编辑

In psql you can simply use the \d+command to show all tables including their comments. Or use the \ddcommand to show all comments in the system

在 psql 中，您可以简单地使用该\d+命令来显示所有表，包括它们的注释。或者使用\dd命令显示系统中的所有评论

回答by Peter Krauss

The main problem with "show comments" is to remember the name of specific fucntions, catalog names, etc. to retrieve the comment... Or its pages on the Guide. At this answer we solve in 2 ways:
by a summary of the ordinary way (the pg-way) to show comments;
and by offering shortcut functions, to reduce the "remember problem".

“显示评论”的主要问题是记住特定功能的名称、目录名称等以检索评论...或其在指南上的页面。在这个答案中，我们以两种方式解决：
通过总结普通方式（pg-way）来显示评论；
并通过提供快捷功能，减少“记忆问题”。

---

---

The pg-way

pg 方式

The simplest, on psql, is to use \dt+to show table comments and \d+to show column comments. Some for function comments?

在最简单的，对psql，就是用\dt+显示表的意见和\d+显示专栏评论。一些用于功能注释？

To get on SQL, and for people that remember all parameters, the pg-way is to use the obj_description()function (Guide) in conjunction with adequate reg-type:

要了解 SQL，并且对于记住所有参数的人，pg 方法是将obj_description()函数 ( Guide) 与足够的reg类型结合使用：

• Function: select obj_description('mySchema.myFunction'::regproc, 'pg_proc')

• Table or View:
  ("... and most everything else that has columns or is otherwise similar to a table",guide)
  select obj_description('mySchema.myClass'::regclass, 'pg_class')

• othergeneric: select obj_description('mySchema.myObject'::regName, pg_regName), where regNameis 1 in 10 of datatype-oid references Guide, and pg_regNameis the same replacing prefix regby prefix pg_.

• otherspecific: similar select obj_description('schema.myObject'::regName, catalog_name), where catalog_nameis to be more specific about a (1 in 95) key-word at catalogs Guide. It can reduce some "namespace pollution". For example pg_procfor functions, pg_aggregatefor aggregate functions.

• to get comment for a shared database object, analog but using the function shobj_description()(same page Guide).

• Column: select col_description('mySchema.myObject'::regClass, column_number), where column_numberis the column's ordinal position (at the CREATE TABLE).
  No column-name... See col_description(table,column_name)complement bellow.

• 功能：select obj_description('mySchema.myFunction'::regproc, 'pg_proc')

• 表或视图：（
  “......以及大多数其他具有列或与表相似的所有内容”，指南）
  select obj_description('mySchema.myClass'::regclass, 'pg_class')

• 其他通用：select obj_description('mySchema.myObject'::regName, pg_regName)，其中regName是 10分之1 的datatype-oid 引用 Guide，并且pg_regName是相同的，用 prefix 替换regprefix pg_。

• 其他特定的：similar select obj_description('schema.myObject'::regName, catalog_name)，其中catalog_name是关于catalogs Guide 中的（1 in 95）关键字更具体的。它可以减少一些“命名空间污染”。例如pg_proc对于函数，pg_aggregate对于聚合函数。

• 获取共享数据库对象的注释，模拟但使用该函数shobj_description()（同一页指南）。

• 列: select col_description('mySchema.myObject'::regClass, column_number)，其中column_number是列的序号位置（在 CREATE TABLE 处）。
  没有列名...见col_description(table,column_name)补充波纹管。

IMPORTANT: the use of same reg-type and _catalog_name_ (e. g. ::regclassand pg_class) seems redundant and sometimes obj_description('obj'::regObj)works fine, with only reg-type! ...But, as the Guide say:

重要提示：使用相同的reg-type 和 _catalog_name_（例如 ::regclassand pg_class）似乎是多余的，有时obj_description('obj'::regObj)工作正常，只有reg-type！...但是，正如指南所说：

it is deprecated since there is no guarantee that OIDs are unique across different system catalogs; therefore, the wrong comment might be returned.

它已被弃用，因为不能保证 OID 在不同的系统目录中是唯一的；因此，可能会返回错误的评论。

Shortcut functions to get comments

获取评论的快捷功能

if you are finding it difficult to remember all the type-casts and parameters, the best is to adopt a new and simplest function to retrieve comments.

如果你发现很难记住所有的类型转换和参数，最好是采用一个新的、最简单的函数来检索评论。

 CREATE FUNCTION rel_description(
     p_relname text, p_schemaname text DEFAULT NULL
 ) RETURNS text AS $f$
    SELECT obj_description((CASE 
       WHEN strpos(, '.')>0 THEN 
       WHEN  IS NULL THEN 'public.'||
       ELSE ||'.'||
            END)::regclass, 'pg_class');
 $f$ LANGUAGE SQL;
 -- EXAMPLES OF USE:
 -- SELECT rel_description('mytable'); 
 -- SELECT rel_description('public.mytable'); 
 -- SELECT rel_description('otherschema.mytable'); 
 -- SELECT rel_description('mytable', 'otherschema'); 
 -- PS: rel_description('public.mytable', 'otherschema') is a syntax error, 
 --     but not generates exception: returns the same as ('public.mytable') 

We need also something less ugly to show column comments. There are no kind of pg_get_serial_sequence()function to get ordinal position of a columnfrom its name. The native
col_description('mySchema.myObject'::regClass, column_number)
needs a complement:

我们还需要一些不那么难看的东西来显示专栏评论。没有任何pg_get_serial_sequence()函数可以从名称中获取列的顺序位置。本机
col_description('mySchema.myObject'::regClass, column_number)
需要一个补充：

 CREATE FUNCTION col_description(
    p_relname text,  -- table name or schema.table 
    p_colname text,   -- table's column name
    p_database text DEFAULT NULL -- NULL for current
 ) RETURNS text AS $f$
    WITH r AS (
      SELECT CASE WHEN array_length(x,1)=1 THEN array['public',x[1]] ELSE x END
      FROM regexp_split_to_array(p_relname,'\.') t(x)
     ) 
    SELECT col_description(p_relname::regClass, ordinal_position)
    FROM r, information_schema.columns i
    WHERE i.table_catalog = CASE 
        WHEN  IS NULL THEN current_database() ELSE  
      END and i.table_schema  = r.x[1]
      and i.table_name    = r.x[2]
      and i.column_name = p_colname
 $f$ LANGUAGE SQL;
 -- SELECT col_description('tableName','colName'); 
 -- SELECT col_description('schemaName.tableName','colName','databaseName); 

---

---

NOTES:

笔记：

• As recommended by this answer: "If you want to know which queries does psql run when you do \dt+ or \d+ customers, just launche it with psql -E".

• It is possible to express multiline comment, using any multiline string(with E\nor $$...$$)...
  But you can't apply trim()or use another dynamic aspect. Must use dynamic SQLon COMMENTclause for it.

• No comments to see? PostgreSQL programmers not use COMMENTclause because it is ugly to use: there are no syntax to add comment on CREATE TABLE or on CREATE FUNCTION; and there are no good IDE to automatize it.

• The modern http://postgREST.org/interface show comments on the Web!

• 正如此答案所建议的：“如果您想知道在执行 \dt+ 或 \d+ 客户时 psql 运行哪些查询，只需使用psql -E“启动它。

• 可以使用任何多行字符串（带有 E\n或$$...$$）来表达多行注释……
  但是您不能应用trim()或使用另一个动态方面。必须在COMMENT子句上使用动态 SQL。

• 没有评论看？PostgreSQL 程序员不使用COMMENT子句，因为它使用起来很丑陋：没有语法可以在 CREATE TABLE 或CREATE FUNCTION上添加注释；并且没有好的 IDE 可以自动化它。

• 现代http://postgREST.org/界面在网络上显示评论！