column information from view

On 9/14/18 2:35 PM, Sebastian P. Luque wrote:

Hello,

I'm trying to generate a table with information on columns from a
temporary view that simply selects a subset of columns from a persistent
view in a given schema. The persistent view joins a number of tables
with columns that may or may not have a description entered. I need a
table with a list of columns from the temporary view, and the matching
descriptions from the underlying persistent view.

Here's my attempt at listing the temporary view's columns and respective
descriptions:

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='dbname' AND cols.table_schema='some_schema' AND
cols.table_name = 'persistent_view' AND cols.table_name = cl.relname
ORDER BY cols.ordinal_position::INT;

The problem, of course, is that it lists columns from the persistent
view, instead of the subset of them in the temporary view. Is there a
better way to do that? Hopefully this makes sense.

create temp view c_data as select source_id, geography_desc from
catfish_data ;

\d c_data
View "pg_temp_3.c_data"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+---------
source_id | integer | | |
geography_desc | character varying |

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

Thanks,
--
Seb

--
Adrian Klaver
adrian.klaver@aklaver.com

"Sebastian P. Luque" <spluque@gmail.com> writes:

Here's my attempt at listing the temporary view's columns and respective
descriptions:

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='dbname' AND cols.table_schema='some_schema' AND
cols.table_name = 'persistent_view' AND cols.table_name = cl.relname
ORDER BY cols.ordinal_position::INT;

The problem, of course, is that it lists columns from the persistent
view, instead of the subset of them in the temporary view. Is there a
better way to do that? Hopefully this makes sense.

Umm ... why are you doing cols.table_name = 'persistent_view'
and not cols.table_name = 'temporary_view' ?

It seems rather odd to write a query that involves both pg_class
and the information_schema --- by involving pg_class, you've already
given up hope of making the query portable to non-PG DBMSes.

Personally, I'd probably write it something like this:

select pa.attnum, pa.attname, col_description(pa.attrelid, pa.attnum)
from
pg_attribute pa, pg_attribute ta
where
pa.attrelid = 'persistent_view'::regclass and
ta.attrelid = 'temporary_view'::regclass and
pa.attname = ta.attname
order by pa.attnum;

If you were dealing with tables, it'd also be wise to add
"pa.attnum > 0 and not pa.attisdropped", but I think neither of
those conditions can fail for views.

regards, tom lane

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.

Always learning something here.

Thanks,
--
Seb

On Fri, 14 Sep 2018 17:52:28 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

Umm ... why are you doing cols.table_name = 'persistent_view' and not
cols.table_name = 'temporary_view' ?

I should have pointed out that the column descriptions are all NULL in
the temporary view, and I'd like to pull them from the persistent view
which have the same name. I know this is brittle though.

It seems rather odd to write a query that involves both pg_class and
the information_schema --- by involving pg_class, you've already given
up hope of making the query portable to non-PG DBMSes.

Personally, I'd probably write it something like this:

select pa.attnum, pa.attname, col_description(pa.attrelid, pa.attnum)
from pg_attribute pa, pg_attribute ta where pa.attrelid =
'persistent_view'::regclass and ta.attrelid =
'temporary_view'::regclass and pa.attname = ta.attname order by
pa.attnum;

If you were dealing with tables, it'd also be wise to add "pa.attnum >
0 and not pa.attisdropped", but I think neither of those conditions
can fail for views.

Thank you Tom, this does seem more elegant, but I'd have to retrieve the
actual "attrelid" from the names of the two views somehow. I'm very
green on using these internal database tables.

--
Seb

"Sebastian P. Luque" <spluque@gmail.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Personally, I'd probably write it something like this:

select pa.attnum, pa.attname, col_description(pa.attrelid, pa.attnum)
from pg_attribute pa, pg_attribute ta where pa.attrelid =
'persistent_view'::regclass and ta.attrelid =
'temporary_view'::regclass and pa.attname = ta.attname order by
pa.attnum;

Thank you Tom, this does seem more elegant, but I'd have to retrieve the
actual "attrelid" from the names of the two views somehow.

That's what the regclass converter does for you.

regards, tom lane

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

On Fri, 14 Sep 2018 18:29:27 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Sebastian P. Luque" <spluque@gmail.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Personally, I'd probably write it something like this:

select pa.attnum, pa.attname, col_description(pa.attrelid,
pa.attnum) from pg_attribute pa, pg_attribute ta where pa.attrelid =
'persistent_view'::regclass and ta.attrelid =
'temporary_view'::regclass and pa.attname = ta.attname order by
pa.attnum;

Thank you Tom, this does seem more elegant, but I'd have to retrieve
the actual "attrelid" from the names of the two views somehow.

That's what the regclass converter does for you.

Amazing!

Thank you all for these insights,
--
Seb