Thread: Finding description pg_description

How do I find the source of an objoid from pg_catalog.pg_description?  I 
comment everything in my databases and can find most of the comments in 
pg_catalog.pd_description, which only gives me objoid and classoid for 
the source of a comment.  If I could find the oid sources I could make 
this work.  I can find what I need for tables, columns, functions and a 
few other things but I cannot complete loops for foreign_data_wrappers, 
schemas, triggers and foreign keys.

For example, I created a foreign_data_wrapper comment and can find it 
with this query:
select * from pg_catalog.pg_description where description like '%FDW%';
102432;1259;0;"Alias: FDW - Foreign data wrapper that acquires data from 
a remote database as specified in the column: 
devops.stp2_foreign_data_wrappers.remote_db_connection."

...but I have no idea where the objoid is coming from so I can't join it 
to anything programmatically.

Here is the DDL for schemas that I'm trying to finish:

-- drop view devops.${DBNAME}_schemas;
create view devops.${DBNAME}_schemas
       (schema_name
       ,object_type
       ,schema_description
       )
as
select s.schema_name
       ,'Schema'::text     -- for validation log file
       ,pd.description
   from information_schema.schemata s
        left join pg_description  pd
               on (pd.objoid = ??.oid )   --- what do I join to?????
  where s.schema_name not in 
('dba','information_schema','pg_catalog','public')
;
comment on view devops.${DBNAME}_schemas is 'Alias: SCH - Master view of 
all ${DBNAME} schemas. Each schema has a purpose and provides a safe 
habitat for its business data and functionality.';

In contrast, the following view works perfectly as planned since I know 
how to find the oid:
-- drop view devops.${DBNAME}_functions;
create view devops.${DBNAME}_functions (
        schema
       ,function_name
       ,function_arguments
       ,function_description
       ) as
select pn.nspname
       ,pp.proname
       ,pp.proargnames
       ,pd.description
   from pg_proc pp
        left join pg_description  pd
               on (pd.objoid = pp.oid )
       ,pg_namespace pn
  where pn.oid = pp.pronamespace
    and pn.nspname not in 
('dba','pg_catalog','information_schema','public')
  order by pn.nspname
          ,pp.proname
;
comment on view devops.${DBNAME}_functions is 'Alias: FN - Master view 
of all ${DBNAME} functions and their arguments from all ${DBNAME} 
schemas.';

Thanks for your help!

Sue
-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

Susan Hurst <susan.hurst@brookhurstdata.com> writes:
> How do I find the source of an objoid from pg_catalog.pg_description?

The classoid column holds the OID of the system catalog that contains
the object's defining row.  The objoid column is the OID of the object,
ie the "oid" column of that row.  (If you are working with a pre-v12
PG release you might be confused by the fact that the oid column is
hidden by default.  But it's there and you can select it.)

Depending on what you're doing, you might prefer to use the
pg_describe_object() function to decipher those columns:

regression=# select classoid::regclass, pg_describe_object(classoid, objoid, objsubid), description from pg_description
limit5; 
 classoid |        pg_describe_object         |                     description
----------+-----------------------------------+------------------------------------------------------
 pg_proc  | function ts_debug(regconfig,text) | debug function for text search configuration
 pg_proc  | function ts_debug(text)           | debug function for current text search configuration
 pg_proc  | function boolin(cstring)          | I/O
 pg_proc  | function boolout(boolean)         | I/O
 pg_proc  | function byteain(cstring)         | I/O
(5 rows)

Alternately, locutions like "where classoid = 'pg_proc'::regclass"
might be helpful.

            regards, tom lane