CODEWITHSUNDEEP
sqlpostgresqldescriptor
Peter Krauss
Peter Krauss

Reputation: 13930

How to see defined comments on database objects?

The main motivation to use COMMENT ON IS, is to generate smart documentation reports, manipulating it by SQL.

The Guide postgresql.org/docs/functions-info not explains (is ugly). I try intuitive SELECT obj_description('schemaName.tableName'::regclass) and luckily worked... But I need

  1. To do the same with function names, but name::regclass not works with functions.

  2. To see what is catalog_name? For functions, tables and views.

  3. How to easilly (in nowadays 2018) list all the functions or all the tables of a schema?

Upvotes: 1

Views: 446

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51496

so=# create function t.fn() returns int as
$$
begin return 1; end; $$ language plpgsql;
CREATE FUNCTION
so=# comment on function t.fn() is 'some comment';
COMMENT
so=# select * from obj_description('t.fn'::regproc);
 obj_description
-----------------
 some comment
(1 row)

regclass is for relations, for function use regproc

update

https://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE

The two-parameter form of obj_description returns the comment for a database object specified by its OID and the name of the containing system catalog. For example, obj_description(123456,'pg_class') would retrieve the comment for the table with OID 123456. The one-parameter form of obj_description requires only the object OID. It is deprecated since there is no guarantee that OIDs are unique across different system catalogs; therefore, the wrong comment might be returned.

functions oids are stored in pg_proc, tables and views in pg_class (relkind r and v respectively), thus:

  1. select * from obj_description('t.fn'::regproc)
  2. pg_class for tables and views, pg_proc for functions
  3. below are queries:

for all functions (I add order and limit to show UDF small list):

so=# select oid::regproc,obj_description(oid,tableoid::regclass::text) 
from pg_proc 
order by oid desc 
limit 2;
 oid  | obj_description
------+-----------------
 t.fn | some comment
 a    | blah
(2 rows)

for all tables:

so=# select oid::regclass,obj_description(oid,tableoid::regclass::text) from pg_class where relkind = 'r' order by oid desc limit 1;
      oid      |   obj_description
---------------+---------------------
 t."WeirdMix$" | table with bad name
(1 row)

respectively for views:

so=# select oid::regclass,obj_description(oid,tableoid::regclass::text) from pg_class where relkind = 'v' order by oid desc limit 1;
 oid | obj_description
-----+-----------------
 v   | view this is
(1 row)

Upvotes: 1

Related Questions