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