Re: column information from view

"Sebastian P. Luque" <spluque(at)gmail(dot)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