column information from view
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. Thanks, -- Seb
Re: column information from view
On Fri, 14 Sep 2018 14:47:07 -0700, Adrian Klaver 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
Re: column information from view
On Fri, 14 Sep 2018 17:52:28 -0400, Tom Lane 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
Re: column information from view
On Fri, 14 Sep 2018 18:29:27 -0400, Tom Lane wrote: > "Sebastian P. Luque" writes: >> Tom Lane 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
