如何从PostgreSQL数据库获取角色的注释 [英] How to get role's comment from the PostgreSQL database

问题描述

给出角色名称 someone ，如何获得带子句定义的注释：

Given the role name someone, how to get a comment defined with clause:

对角色的评论有人是这是一个角色评论；

？

推荐答案

查找此类信息的一种好方法是检查 psql 的帮助并找到以下命令将显示此信息。

A good way to find how to retrieve this kind of information is to check the help for psql and find the command that would display this information.

在这种情况下，它是 dg + 命令。

In this case, it is the dg+ command.

一旦您知道该命令，便可以启用 psql 工具的内部SQL查询的打印以查找它如何检索该信息。这是通过使用 -E 参数启动 psql 来完成的。

Once you know the command, you can enable printing of the internal SQL queries of the psql tool to find out how it retrieves that information. This is done by starting psql using the -E parameter.

如果这样做，将会看到：

If you do that, you'll see:

psql (9.4.5)
Type "help" for help.

postgres=# \dg+
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************

                                List of roles
   Role name   |            Attributes             | Member of | Description
---------------+-----------------------------------+-----------+-----------------------
 someone       |                                   | {}        | THIS IS A ROLE COMMENT

从您的问题中尚不清楚您是否只想要一些方法查看注释，那么 dg + 可能就足够了。否则，您可以根据需要调整 psql 使用的SQL查询，例如：

It's not clear from your question if you just want some method to see the comment, then dg+ is probably enough. Otherwise you can adjust the SQL query that is used by psql to your needs, e.g.:

SELECT pg_catalog.shobj_description(r.oid, 'pg_authid') AS description
FROM pg_catalog.pg_roles r
where r.rolname = 'someone';

注意：\du和\dg在psql中是同一命令。出于历史原因，两者都进行维护。

Note: \du and \dg are the same command in psql. Both are maintained for historic reasons.

这篇关于如何从PostgreSQL数据库获取角色的注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！