从 PostgreSQL 数据库中检索评论

Retrieving Comments from a PostgreSQL DB

提问by user609896

I'm running a project on a Postgres database and need to retrieve the comments on columns within the DB to be used as table headings and such. I have seen that there are a couple of built in functions (pg_descriptionand col_description) but i haven't been able to find examples on how to use them and playing around with them has proved pretty futile.

我正在 Postgres 数据库上运行一个项目，需要检索数据库中列的注释以用作表标题等。我已经看到有几个内置函数（pg_description和col_description），但我无法找到有关如何使用它们的示例，并且已经证明使用它们是徒劳的。

So I was wondering if any has been able to do this before and if so, how?

所以我想知道以前是否有人能够做到这一点，如果有，如何做到？

回答by user609896

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);

回答by mat

It all works by oid,

这一切都由oid工作，

mat=> SELECT c.oid FROM pg_catalog.pg_class c WHERE c.relname = 'customers';
  oid  
-------
 23208
(1 row)

Now, I have the oid for that table, so I can ask :

现在，我有那个表的 oid，所以我可以问：

mat=> select pg_catalog.obj_description(23208);
  obj_description  
-------------------
 Customers
(1 row)

Then, I can ask for the description of the fourth column :

然后，我可以要求第四列的描述：

mat=> select pg_catalog.col_description(23208,4);
             col_description             
-----------------------------------------
 Customer codes, CHS, FACTPOST, POWER...
(1 row)

If you want to know which queries does psqlrun when you do \dt+or \d+ customers, just run it with -E.

如果您想知道psql在执行\dt+or时确实运行了哪些查询\d+ customers，只需使用-E.

回答by Marcio Mazzucato

Take care with schemas, this code considers them:

注意模式，此代码考虑它们：

SELECT
    cols.column_name,
    (
        SELECT
            pg_catalog.col_description(c.oid, cols.ordinal_position::int)
        FROM
            pg_catalog.pg_class c
        WHERE
            c.oid = (SELECT ('"' || cols.table_name || '"')::regclass::oid)
            AND c.relname = cols.table_name
    ) AS column_comment
FROM
    information_schema.columns cols
WHERE
    cols.table_catalog    = 'your_database'
    AND cols.table_name   = 'your_table'
    AND cols.table_schema = 'your_schema';

References:

参考：

• Postgresql Document Table and Column Description Comments on Table and Column

• Determining the OID of a table in Postgres 9.1?

• Postgresql文档表和列描述表和列的注释

• 在 Postgres 9.1 中确定表的 OID？

回答by T.Z.

Just to be here if somebody will need it.

如果有人需要它，就在这里。

There are many answers here, but none of them was as simple as I would like it to be. So, based on previous answers and current postgres 9.4, I have created this query:

这里有很多答案，但没有一个像我希望的那样简单。因此，根据以前的答案和当前的 postgres 9.4，我创建了这个查询：

SELECT 
    obj_description(format('%s.%s',isc.table_schema,isc.table_name)::regclass::oid, 'pg_class') as table_description,
    pg_catalog.col_description(format('%s.%s',isc.table_schema,isc.table_name)::regclass::oid,isc.ordinal_position) as column_description
FROM
    information_schema.columns isc

It fetches table and column descriptions, without any confusing joins and ugly string concatenations.

它获取表和列的描述，没有任何令人困惑的连接和丑陋的字符串连接。

回答by T.Z.

This works for me using the PostBooks 3.2.2 DB:

这对我使用 PostBooks 3.2.2 DB 有效：

select cols.column_name,
(select pg_catalog.obj_description(oid) from pg_catalog.pg_class c where c.relname=cols.table_name) as table_comment
,(select pg_catalog.col_description(oid,cols.ordinal_position::int) from pg_catalog.pg_class c where c.relname=cols.table_name) as column_comment
from information_schema.columns cols
where cols.table_catalog='postbooks' and cols.table_name='apapply'

Regards, Sylnsr

问候， Sylnsr

回答by Peter Krauss

Enhance for @Nick and @mat suggestions: use
SELECT obj_description('schemaName.tableName'::regclass, 'pg_class');when you have string name (not oid).

增强@Nick 和@mat 建议：
SELECT obj_description('schemaName.tableName'::regclass, 'pg_class');当您有字符串名称（不是 oid）时使用。

To avoid to remember 'pg_class' parameter, and to avoid ugly concatenations at the function calls, as (tname||'.'||schema)::regclass, an useful overload for obj_description:

为了避免记住 'pg_class' 参数，并避免在函数调用中出现丑陋的串联， as(tname||'.'||schema)::regclass是一个有用的重载obj_description：

  CREATE FUNCTION obj_description(
      p_rname text, p_schema text DEFAULT NULL, 
      p_catalname text DEFAULT 'pg_class'
  ) RETURNS text AS $f$
     SELECT obj_description((CASE 
        WHEN strpos(, '.')>0 OR  IS NULL OR ='' THEN 
        ELSE ||'.'||
     END)::regclass, );
  $f$ LANGUAGE SQL IMMUTABLE;
 -- USAGE: obj_description('mytable') 
 --        SELECT obj_description('s.t'); 
 -- PS: obj_description('s.t', 'otherschema') is a syntax error, 
 --     but not generates exception: returns the same as ('s.t') 

Now is easy to use, because the table name(rnameparameter) is a varchar and can be expressed with a separated field for schema name, as in the main tables and queries.

现在很容易使用，因为表名（rname参数）是一个 varchar 并且可以用模式名称的单独字段表示，就像在主表和查询中一样。

See also "Getting list of table comments in PostgreSQL" or the new pg9.3 Guide

另请参阅“在 PostgreSQL 中获取表注释列表”或新的 pg9.3 指南

回答by DatabaseShouter

A slight change to one of the other answers which only gives you columns that have comments on them, this gives you all columns whether they have a comment or not.

对其他答案之一稍作更改，只为您提供对其有评论的列，这将为您提供所有列，无论它们是否有评论。

select c.table_schema, st.relname as TableName, c.column_name, 
pgd.description
from pg_catalog.pg_statio_all_tables as st
inner join information_schema.columns c
on c.table_schema = st.schemaname
and c.table_name = st.relname
left join pg_catalog.pg_description pgd
on pgd.objoid=st.relid
and pgd.objsubid=c.ordinal_position
where st.relname = 'YourTableName';

回答by James Roscoe

I accessed table comments like this:

我访问了这样的表注释：

select c.relname table_name, pg_catalog.obj_description(c.oid) as comment from pg_catalog.pg_class c where c.relname = 'table_name';

and column comments thusly:

和专栏评论因此：

SELECT 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 and c.table_name = 'table_name' and c.table_schema = 'public');

回答by James Roscoe

This answer is a little late, but it popped up on a google search I did to research this problem. We only needed Table descriptions, but the method would be the same for columns. The column descriptions are in the pg_description table also, referenced by objoid.

这个答案有点晚了，但它突然出现在我研究这个问题的谷歌搜索中。我们只需要表描述，但列的方法是相同的。列描述也在 pg_description 表中，由 objoid 引用。

Add this view:

添加此视图：

CREATE OR REPLACE VIEW our_tables AS 
 SELECT c.oid, n.nspname AS schemaname, c.relname AS tablename, d.description,
   pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", 
   c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0 AS hastriggers
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
   LEFT JOIN pg_description d ON c.oid = d.objoid
  WHERE c.relkind = 'r'::"char";

ALTER TABLE our_tables OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE our_tables TO postgres;
GRANT SELECT ON TABLE our_tables TO public;

Then run:

然后运行：

SELECT tablename, description FROM our_tables WHERE schemaname = 'public'

The view is a modified version of the pg_tables view which adds in the description column. You could also monkey around with the view definition to make it a single query.

该视图是 pg_tables 视图的修改版本，它添加在描述列中。您还可以随意修改视图定义以使其成为单个查询。

回答by dland

I asked a similar question about Postgresql commentslast month. If you dig through that, you'll come across some Perl code over on my blog that automates the process of extracting a comment.

上个月我问了一个关于 Postgresql 评论的类似问题。如果您深入研究，您会在我的博客上看到一些 Perl 代码，这些代码自动执行提取评论的过程。

To pull out the column names of a table, you can use something like the following:

要提取表的列名，您可以使用以下内容：

select
     a.attname  as "colname"
    ,a.attrelid as "tableoid"
    ,a.attnum   as "columnoid"
from
    pg_catalog.pg_attribute a
    inner join pg_catalog.pg_class c on a.attrelid = c.oid
where
        c.relname = 'mytable' -- better to use a placeholder
    and a.attnum > 0
    and a.attisdropped is false
    and pg_catalog.pg_table_is_visible(c.oid)
order by a.attnum

You can then use the tableoid,columnoid tuple to extract the comment of each column (see my question).

然后您可以使用 tableoid,columnoid 元组来提取每列的注释（请参阅我的问题）。