从 PostgreSQL 数据库检索评论

Question

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

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

Answer 1

只要有人需要就在这里。

这里有很多答案，但没有一个像我希望的那么简单。 因此，根据以前的答案和当前的 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

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

Answer 2

这一切都是通过 oid 进行的，

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

现在，我有了该表的 oid，所以我可以询问：

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

然后，我可以询问第四列的描述：

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

如果您想知道 psql 执行哪些查询当您执行 \dt+ 或 \d+customers 时运行，只需使用 -E 运行即可。

Answer 3

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

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

参考文献：

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

• 确定 Postgres 9.1 中表的 OID？

Answer 4

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

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

Answer 5

如果您只需要在其他数据中显示列的注释，您还可以使用：

\d+ my_table

Answer 6

这对我使用 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'

问候，
西林斯尔

Answer 7

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

避免记住“pg_class”参数，并避免在函数调用时出现丑陋的串联，如 (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($1, '.')>0 OR $2 IS NULL OR $2='' THEN $1
        ELSE $2||'.'||$1
     END)::regclass, $3);
  $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') 

现在很容易使用，因为表名称（rname参数）是一个varchar，可以用模式名称的分隔字段来表示，如主表和查询中一样。

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

Answer 8

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

添加此视图：

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;

然后运行：

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

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

Answer 9

我访问了这样的表注释：

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

和列注释：

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

Answer 10

我问了一个类似的问题上个月 Postgresql 评论。 如果您深入研究，您会在我的博客上发现一些 Perl 代码，它可以自动执行提取评论的过程。

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

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

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

Answer 11

我刚在这里找到这个。 它将为您提供一个特定表的所有类型的元数据（类型、默认值、非空标志、长度、注释、外键名称、主键名称）。 看起来效果不错。

SELECT pg_tables.tablename, pg_attribute.attname AS field, 
    format_type(pg_attribute.atttypid, NULL) AS "type", 
    pg_attribute.atttypmod AS len,
    (SELECT col_description(pg_attribute.attrelid, 
            pg_attribute.attnum)) AS comment, 
    CASE pg_attribute.attnotnull 
        WHEN false THEN 1  ELSE 0  
    END AS "notnull", 
    pg_constraint.conname AS "key", pc2.conname AS ckey, 
    (SELECT pg_attrdef.adsrc FROM pg_attrdef 
        WHERE pg_attrdef.adrelid = pg_class.oid 
        AND pg_attrdef.adnum = pg_attribute.attnum) AS def 
FROM pg_tables, pg_class 
JOIN pg_attribute ON pg_class.oid = pg_attribute.attrelid 
    AND pg_attribute.attnum > 0 
LEFT JOIN pg_constraint ON pg_constraint.contype = 'p'::"char" 
    AND pg_constraint.conrelid = pg_class.oid AND
    (pg_attribute.attnum = ANY (pg_constraint.conkey)) 
LEFT JOIN pg_constraint AS pc2 ON pc2.contype = 'f'::"char" 
    AND pc2.conrelid = pg_class.oid 
    AND (pg_attribute.attnum = ANY (pc2.conkey)) 
WHERE pg_class.relname = pg_tables.tablename  
--    AND pg_tables.tableowner = "current_user"() 
    AND pg_attribute.atttypid <> 0::oid  
    AND tablename='your_table' 
ORDER BY field ASC

来源：http://golden13.blogspot.de /2012/08/how-to-get-some-information-about_7.html

Answer 12

好的，所以我在某种程度上解决了这个问题...

select col_description(table id, columns number)...

即： select col_description(36698,2);

这可行，但是有没有更简单的方法来做到这一点，也许可以将所有注释都放在所有列上，并使用表名而不是 oid？？？

Answer 13

要显示所有表的所有列的注释：

SELECT
    cols.table_name,
    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_name   IN (SELECT cols.table_name FROM information_schema.columns)
  AND cols.table_catalog = 'your_database_name'
  AND cols.table_schema = 'your_schema_name';

您需要在任何模式/目录/数据库之外执行此查询

此查询基于此问题中的另一个答案，该答案仅显示来自一个表的注释

Answer 14

扩展@amxy 提供的响应； 我发现添加架构过滤器在某些环境中会有所帮助。 我发现 @amxy 的解决方案在我通过架构过滤器添加之前不起作用

SELECT 
pg_tables.schemaname,
pg_tables.TABLENAME,
pg_attribute.attname AS field,
format_type(pg_attribute.atttypid, NULL) AS "type",
pg_attribute.atttypmod AS len,
(
SELECT col_description(pg_attribute.attrelid, pg_attribute.attnum)) AS  COMMENT,
CASE pg_attribute.attnotnull
    WHEN FALSE THEN 1
    ELSE 0
END AS "notnull",
pg_constraint.conname AS "key", pc2.conname AS ckey,
(
SELECT pg_attrdef.adsrc
FROM pg_attrdef
WHERE pg_attrdef.adrelid = pg_class.oid
    AND pg_attrdef.adnum = pg_attribute.attnum) AS def
FROM pg_tables, pg_class
JOIN pg_attribute
    ON pg_class.oid = pg_attribute.attrelid
    AND pg_attribute.attnum > 0
LEFT JOIN pg_constraint
    ON pg_constraint.contype = 'p'::"char"
    AND pg_constraint.conrelid = pg_class.oid
    AND
(pg_attribute.attnum = ANY (pg_constraint.conkey))
LEFT JOIN pg_constraint AS pc2
    ON pc2.contype = 'f'::"char"
    AND pc2.conrelid = pg_class.oid
    AND (pg_attribute.attnum = ANY (pc2.conkey))
WHERE pg_class.relname = pg_tables.TABLENAME
AND pg_tables.schemaname IN ('op', 'im', 'cs','usr','li')
-- AND pg_tables.tableowner = "current_user"()
    AND pg_attribute.atttypid <> 0::oid
    ---AND TABLENAME='your_table'
ORDER BY pg_tables.schemaname,
pg_tables.TABLENAME ASC;

结果：

Answer 15

SELECT 
    relname table_name,
    obj_description(oid) table_description,
    column_name,
    pgd.description column_description
FROM pg_class
INNER JOIN
    information_schema.columns
        ON table_name = pg_class.relname
LEFT JOIN 
    pg_catalog.pg_description pgd
        ON pgd.objsubid = ordinal_position
WHERE 
    relname = 'your_table_name'

Answer 16

SELECT sc.table_schema、sc.table_name、sc.column_name、col_description(pc."oid"、sc.ordinal_position) col_description FROM pg_class pc
INNER JOIN pg_namespace ns ON ns."oid" =pc.relnamespace
内连接information_schema。列sc ON sc.table_name=pc.relname AND sc.table_schema=ns.nspname
哪里1=1
  AND upper(ns.nspname) = 'TABLE_SCHEMA'
  和 上层(pc.relname)   = 'TABLE_NAME'

从 PostgreSQL 数据库检索评论

Answer 17

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