postgresql - 如何在 PostgreSQL 中显示所有对象及其注释？

Question

作为一个正在进行的项目，我的任务是评论 postgres 数据库中的所有内容，从而记录所有对象及其所做的事情。是否有一个 SELECT 脚本可以显示所有表、 View 、列和函数等及其注释，包括带有空注释的注释。以下是我所追求的示例:

SELECT object_type,object_schema,object_name,comment
FROM information_schema.some_table_out_there;

|object_type|object_schema|object_name|comment|
|table      | my_schema   |table1     |my first table
|table      | my_schema   |table2     |NULL
|view       | public      |employees  |NULL
|function   | public      |emps_insert|inserts employees

我想使用这个脚本来创建一个报告，您可以在其中钻取一个表单并在数据库对象上评论您想要的评论。

Answer 1

information_schema 由 ANSI standard 定义，所以它只涵盖 SQL 规范描述的对象。不包括索引，也不包括任何 Postgres 特定的内容(事件触发器、行安全策略等)。如果你想要一份详尽的 list ，你需要去 system catalogs .

没有中央对象列表；每个对象类型都存在于自己的表中，因此您需要分别查询它们中的每一个。然而，有一些方便 object information functions它将接受任何对象类型，因此我们可以很容易地使用一些动态 SQL 将这些查询粘合在一起:

create function describe_all_objects()
  returns table(
    type text,
    schema text,
    name text,
    identity text,
    comment text
  )
as $$
declare
  /* Cutoff for system object OIDs; see comments in src/include/access/transam.h */
  MIN_USER_OID constant oid = 16384;
  catalog_class regclass;
begin
  for catalog_class in
    /* Get a list of all catalog tables with an OID */
    select oid::regclass
    from pg_class
    where
      relhasoids and
      pg_class.oid < MIN_USER_OID and
      /* Enum members have no obj_description(); the enum itself is picked up in pg_type */
      pg_class.oid <> 'pg_enum'::regclass
  loop
    return query execute format(
      $SQL$
        /* Get descriptions for all user-created catalog entries */
        select
          info.type,
          info.schema,
          info.name,
          info.identity,
          coalesce(
            obj_description(catalog_table.oid, catalog_table.tableoid::regclass::text),
            shobj_description(catalog_table.oid, catalog_table.tableoid::regclass::text)
          ) as comment
        from
          %s as catalog_table,
          lateral pg_identify_object(catalog_table.tableoid, catalog_table.oid, 0) as info
        where
          catalog_table.oid >= %s
      $SQL$,
      catalog_class,
      MIN_USER_OID
    );
  end loop;

  /* Handle "sub-objects" (i.e. pg_attribute) separately */
  return query
    select 
      info.type,
      info.schema,
      info.name,
      info.identity,
      col_description(attrelid, attnum) as comment
    from
      pg_attribute,
      lateral pg_identify_object('pg_class'::regclass, attrelid, attnum) as info
    where
      attrelid >= MIN_USER_OID and
      attnum >= 0 and
      not attisdropped;
end
$$
language plpgsql stable;

select * from describe_all_objects();

这应该涵盖数据库中的每个对象，直到隐式表数组类型和 TOAST 表索引上的列，以及服务器范围的对象，如数据库和用户，所以你可能会想对此进行相当多的过滤。

少数目录表需要 super 用户权限才能直接访问，但如果这是一个问题，您应该能够修改查询以从某些公共(public)资源中提取信息(例如 pg_authid 是仅限 super 用户的，因为它包含密码信息，但您可以改为查看 pg_roles)。

如果您发现任何遗漏请告诉我(请比我在将它用于任何重要的事情之前更彻底地测试它:))。