时间戳

首页 > 解决方案 > 获取具体化视图中的列列表

postgresql - 获取具体化视图中的列列表

问题描述

我有下表：

- 桌子：

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

上表中的表、视图和物化视图中的列很少。

查询：想要查找具有这些列的表、视图和具体化视图名称。

尝试：

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

标签： postgresqlpostgresql-12

解决方案

假设你说你想找到所有物化视图在你的表中有一些列，你会想要这样的东西：

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

我将此答案用作 CTE 查找列名的灵感。

不过，唯一的问题是，至少在我的情况下，我在多个表中有相同的列，结果看起来像这样

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

因此，您可能不想使用 LEFT OUTER JOINS，而是使用 UNION，因此一切都井井有条：

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
)

巧妙地将它们放在一起：

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

推荐阅读