"Sebastian P. Luque" <spluque@xxxxxxxxx> 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
