Displaying Comments in Views

Susan Hurst <susan.hurst@brookhurstdata.com> writes:

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.

No surprise, since you're using pg_statio_all_tables as the source of
tables, and that contains, well, only tables.

I'm not quite sure why you'd choose that view anyway. Personally I'd
have gone directly to pg_class, and then probably filtered on relkind
if there were things I didn't want to see. Or you could use
information_schema.tables.

Also, I'm too lazy to check on how information_schema.columns defines
"ordinal_position", but I wonder if it tries to leave out dropped
columns, or might do so in future. That puts this join condition
at risk: "pd.objsubid = c.ordinal_position".

You'd likely be better off to join pg_class and pg_attribute to
pg_description, rather than working with proxies for them.

https://www.postgresql.org/docs/current/catalogs.html

regards, tom lane

"Susan" == Susan Hurst <susan.hurst@brookhurstdata.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)

On 1/28/19 7:08 AM, Susan Hurst wrote:

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
;

In addition to the suggestions from Tom and Andrew, a tip for future use.

Using psql:

COMMENT ON VIEW test_view IS 'test';

\dv+ test_view
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+------+----------+---------+-------------
public | test_view | view | postgres | 0 bytes | test

Now start psql using -E:

psql -d test -E -U aklaver

\dv+ test_view
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN
'table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname OPERATOR(pg_catalog.~) '^(test_view)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

A good way to see what catalog tables you need to use and how to query them.

--
Adrian Klaver
adrian.klaver@aklaver.com

Thx for the great info. I appreciate your pointing me in the right
direction.

Sue

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