From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Sebastian P(dot) Luque" <spluque(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: column information from view |
Date: | 2018-09-14 21:52:28 |
Message-ID: | 2046.1536961948@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2018-09-14 22:02:42 | Re: commit timestamps and replication |
Previous Message | Adrian Klaver | 2018-09-14 21:47:07 | Re: column information from view |