From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Susan Hurst <susan(dot)hurst(at)brookhurstdata(dot)com> |
Cc: | Discuss List Postgres <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Displaying Comments in Views |
Date: | 2019-01-28 15:41:56 |
Message-ID: | 87lg34izi5.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "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)
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-01-28 15:47:49 | Re: Displaying Comments in Views |
Previous Message | Tom Lane | 2019-01-28 15:27:58 | Re: Displaying Comments in Views |