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@xxxxxxxxxxxxxxxxxx
Mobile: 314-486-3261
