Get list of columns in materialize view

Question

I have the following table:

--Table:

create table tbl_columns
(
 id int,
 col varchar(50)
);

I have few columns from tables, views and materialize views in the above table.

Query: Want to find the tables,views and materialize views name's which are having those columns.

Try:

select t1.col,
        clm.tabl_name,
        vs.view_name,
        --Find materialize view
from tbl_columns t1
left join information_schema.columns clm on t1.col = clm.column_name 
left join information_schema.view_column_usage vs on t1.col = vs.column_name 
left join pg_matviews on .........?

The question is not clear (do you know the mat view name and want to test for column existence, or you want to find any mat view having the said column name, or you want to list all column name for a given mat view, or for a table too, or...). You may find this [post](https://stackoverflow.com/questions/31119260/column-names-and-data-types-for-materialized-views-in-postgresql/33064815#33064815) useful — JGH, Jul 22 '20 at 14:15
@JGH, I have list of columns I just want to find any mat views having those columns? If yes then display mat view name. — MAK, Jul 22 '20 at 14:17

score 1 · Accepted Answer · answered Jul 22 '20 at 21:42

Supposing you're saying you want to find all materialized views have some of the columns in your table, you'll want something like this:

WITH tbl_columns AS (SELECT UNNEST(ARRAY ['col1', 'col2', 'col3']) as col),
     mat_view_columns AS (
         SELECT mv.matviewname,
                a.attname as col
         FROM pg_attribute a
                  JOIN pg_class t on a.attrelid = t.oid
                  JOIN pg_matviews mv on mv.matviewname = t.relname
         WHERE a.attnum > 0
           AND NOT a.attisdropped
     )
select t1.col,
       clm.table_name,
       vs.view_name,
       matviewname
from tbl_columns t1
         left join information_schema.columns clm on t1.col = clm.column_name
         left join information_schema.view_column_usage vs on t1.col = vs.column_name
         left join mat_view_columns on t1.col = mat_view_columns.col

I used this answer as inspiration for the CTE to find the column names.

The only problem, though, is that at least in my case, I have the same column in multiple tables, and the result looks like this

+--------+-------------+---------+-------------+
|col     |table_name   |view_name|matviewname  |
+--------+-------------+---------+-------------+
|col1    |table1       |NULL     |mat_view_1   |
|col2    |table1       |NULL     |mat_view_1   |
|col2    |table2       |NULL     |mat_view_1   |
|col3    |NULL         |NULL     |mat_view_1   |
+--------+-------------+---------+-------------+

So you'll probably want to not use LEFT OUTER JOINS, but a UNION, so everything gets neatly organised:

WITH tbl_columns AS (SELECT UNNEST(ARRAY ['age', 'fte', 'location']) as col)
SELECT col,
       'table' as type,
       table_name
FROM tbl_columns
         JOIN information_schema.columns on col = column_name
UNION ALL
(
    SELECT col,
           'view',
           view_name
    FROM tbl_columns
             join information_schema.view_column_usage on col = column_name
)
UNION ALL
(
    SELECT col,
           'materialized_view',
           matviewname
    FROM pg_attribute a
             JOIN pg_class t on a.attrelid = t.oid
             JOIN pg_matviews mv on mv.matviewname = t.relname
             JOIN tbl_columns on col = a.attname
    WHERE a.attnum > 0
      AND NOT a.attisdropped
)

which neatly puts them under each other:

+--------+-------------+----------+
|col     |type         |table_name|
+--------+-------------+----------+
|col1    |table        |table1    |
|col2    |table        |table1    |
|col2    |table        |table2    |
|col1    |material_view|mat_view_1|
|col2    |material_view|mat_view_1|
|col2    |material_view|mat_view_1|
+--------+-------------+----------+

Get list of columns in materialize view

1 Answers1