Re: column information from view

[Adrian Klaver]

On 9/14/18 3:17 PM, Sebastian P. Luque wrote:

On Fri, 14 Sep 2018 14:47:07 -0700,
Adrian Klaver <adrian.kla...@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.kla...@aklaver.com