Re: column information from view

On 9/14/18 3:17 PM, Sebastian P. Luque wrote:
> On Fri, 14 Sep 2018 14:47:07 -0700,
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
>> SELECT cols.ordinal_position, cols.column_name,
>> col_description(cl.oid, cols.ordinal_position::INT)
>> FROM pg_class cl, information_schema.columns cols
>> WHERE cols.table_catalog='aquaculture' AND cols.table_schema ilike
>> 'pg_temp%' AND
>> cols.table_name = 'c_data' AND cols.table_name = cl.relname
>> ORDER BY cols.ordinal_position::INT;
>
>> ordinal_position | column_name | col_description
>> ------------------+----------------+-----------------
>> 1 | source_id | NULL
>> 2 | geography_desc | NULL
>
> Exactly, except that the column descriptions reside in the persistent
> view whereas the above pulls them from the temporary view, which are all
> NULL.

COMMENT ON column catfish_data.source_id IS 'The source';

SELECT cols.ordinal_position, cols.column_name,
col_description('catfish_data'::regclass, cols.ordinal_position::INT)
FROM
pg_class AS cl
JOIN
information_schema.columns AS cols
ON
cl.relname = cols.table_name
JOIN
information_schema.columns AS cols2
ON
cols.column_name = cols2.column_name
WHERE
cols.table_catalog='aquaculture'
AND
cols2.table_name = 'c_data'
AND
cols.table_schema = 'public'
AND
cols.table_name = 'catfish_data'
;

ordinal_position | column_name | col_description
------------------+----------------+-----------------
2 | source_id | The source
5 | geography_desc | NULL

>
> Always learning something here.
>
> Thanks,
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com