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@xxxxxxxxxxx
