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.klaver@aklaver.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.klaver@aklaver.com