Обсуждение: Displaying Comments in Views
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.hurst@brookhurstdata.com
Mobile: 314-486-3261
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 On 2019-01-28 09:27, Tom Lane wrote: > 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