How to show all objects and their comments in PostgreSQL?

Question

I have been tasked as an ongoing project to comment everything in the postgres database thereby documenting all of the object and what they do. Is there a SELECT script out there that would show all the tables, views, columns, and functions, etc and their comments including the ones with null comments. Below is an example of what I am after:

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

I'd like to used this script to create a report where you drill to a form and comment on the database object your desired comment.

This may help to get you started https://stackoverflow.com/questions/769683/show-tables-in-postgresql — Juan Carlos Oropeza, May 28 '19 at 18:57

Nick Barnes · Accepted Answer · 2019-05-31T23:12:41.780

information_schema is defined by an ANSI standard, so it only covers objects described by the SQL spec. Indexes aren't included, nor is anything Postgres-specific (event triggers, row security policies, etc.). If you want an exhaustive list, you'll need to go to the system catalogs.

There is no central list of objects; each object type lives in its own table, so you need to query each of them separately. However, there are some handy object information functions which will accept any object type, so we can glue these queries together fairly easily with some dynamic 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();

This should cover every object in the database, right down to the implicit table array types and the columns on the TOAST table indexes, as well as server-wide objects like databases and users, so you'll likely want to filter this down quite a bit.

A handful of catalog tables need superuser permission to access directly, but if this is an issue, you should be able to modify the queries to pull the information from some public source (e.g. pg_authid is superuser-only because it contains password information, but you can look at pg_roles instead).

Let me know if you notice any omissions (and please test this more thoroughly than I did before using it for anything important :) ).

How to show all objects and their comments in PostgreSQL?

1 Answers1