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 .........?

Answer 1

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|
+--------+-------------+----------+