Displaying Comments in Views

From: Susan Hurst <susan(dot)hurst(at)brookhurstdata(dot)com>
To: Discuss List Postgres <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Displaying Comments in Views
Date: 2019-01-28 15:08:13
Message-ID: 8d48b89ca871afcce64a6b78d2db2b0c@mail.brookhurstdata.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

What is the trick for displaying column comments in views?

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

There must be a way to display comments if I can display the
table/column definitions, especially since the query joins directly to
information_schema columns. What am I missing?

Thanks for your help!

Sue

select c.table_schema
,c.table_name
,c.column_name
,pd.description
from pg_catalog.pg_statio_all_tables st
,pg_catalog.pg_description pd
,information_schema.columns c
where pd.objoid = st.relid
and pd.objsubid = c.ordinal_position
and c.table_schema = st.schemaname
and c.table_name = st.relname
and c.table_schema = 'devops'
order by c.table_schema
,c.table_name
,c.column_name
;

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan(dot)hurst(at)brookhurstdata(dot)com
Mobile: 314-486-3261

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-01-28 15:11:17 Re: Regarding query execution for long time
Previous Message Karsten Hilbert 2019-01-28 14:47:07 Re: multi-SQL command string aborts despite IF EXISTS