On 9/14/18 3:17 PM, Sebastian P. Luque wrote:
On Fri, 14 Sep 2018 14:47:07 -0700,
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
