Re: column information from view

On Fri, 14 Sep 2018 17:52:28 -0400,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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