Обсуждение: 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
On 9/14/18 2:35 PM, Sebastian P. Luque wrote:
> 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.
create temp view c_data as select source_id, geography_desc from
catfish_data ;
\d c_data
View "pg_temp_3.c_data"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+---------
source_id | integer | | |
geography_desc | character varying |
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
>
> Thanks,
> --
> Seb
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
"Sebastian P. Luque" <spluque@gmail.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
On Fri, 14 Sep 2018 14:47:07 -0700, Adrian Klaver <adrian.klaver@aklaver.com> 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
On Fri, 14 Sep 2018 17:52:28 -0400, Tom Lane <tgl@sss.pgh.pa.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
"Sebastian P. Luque" <spluque@gmail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> 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.
regards, tom lane
On 9/14/18 3:17 PM, Sebastian P. Luque wrote:
> On Fri, 14 Sep 2018 14:47:07 -0700,
> Adrian Klaver <adrian.klaver@aklaver.com> 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.
COMMENT ON column catfish_data.source_id IS 'The source';
SELECT cols.ordinal_position, cols.column_name,
col_description('catfish_data'::regclass, cols.ordinal_position::INT)
FROM
pg_class AS cl
JOIN
information_schema.columns AS cols
ON
cl.relname = cols.table_name
JOIN
information_schema.columns AS cols2
ON
cols.column_name = cols2.column_name
WHERE
cols.table_catalog='aquaculture'
AND
cols2.table_name = 'c_data'
AND
cols.table_schema = 'public'
AND
cols.table_name = 'catfish_data'
;
ordinal_position | column_name | col_description
------------------+----------------+-----------------
2 | source_id | The source
5 | geography_desc | NULL
>
> Always learning something here.
>
> Thanks,
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, 14 Sep 2018 18:29:27 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Sebastian P. Luque" <spluque@gmail.com> writes: >> Tom Lane <tgl@sss.pgh.pa.us> 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