Re: Displaying Comments in Views

>>>>> "Susan" == Susan Hurst <susan(dot)hurst(at)brookhurstdata(dot)com> writes:

Susan> What is the trick for displaying column comments in views? The
Susan> query below works as expected when the table_schema includes
Susan> tables, however it shows nothing when the table_schema contains
Susan> only views. I tried putting the query into an inline statement
Susan> as a column selection in a wrapper query...I got all the
Susan> table/column data but the comment column values were all null.

Susan> from pg_catalog.pg_statio_all_tables st

That is the wrong place to look for the purposes of this query, since as
the name implies it only shows tables (and not views, since
non-materialized views don't have or need I/O statistics). Also, it's
_NOT_ the place to look when you just want a list of tables in the db or
to look up tables by oid; use pg_class for that.

I'd have gone with something along the lines of:

select n.nspname as table_schema,
c.relname as table_name,
a.attname as column_name,
pd.description as description
from pg_class c
join pg_namespace n on (n.oid=c.relnamespace)
join pg_attribute a on (a.attrelid=c.oid
and a.attnum > 0
and not a.attisdropped)
join pg_description pd
on (pd.classoid='pg_class'::regclass
and pd.objoid=c.oid
and pd.objsubid=a.attnum)
where n.nspname = 'devops'
order by n.nspname, c.relname, a.attname;

--
Andrew (irc:RhodiumToad)