如何发现我创建的实例化视图的基础查询？ [英] How do I discover the underlying query of a materialized view I created?

问题描述

我在Postgres 9.3中创建了一个物化视图，但是此后我失去了创建它的基础SELECT查询。我想删除实例化视图，重写查询以包含更多数据，然后创建具有相同名称但具有新基础查询的实例化视图。

解决方案

只需：

  SELECT pg_get_viewdef（'myview'）; 
  

例如在 psql 中：

  test =>从generate_series（1,100）x中选择x创建材料视图
选择100 
 test => 
输出格式未对齐。 
仅显示元组。 
 test => SELECT pg_get_viewdef（'fred'）; 
选择x.x 
 FROM generate_series（1，100）x（x）; 
  

此功能适用于普通视图和实例化视图。

或者，正如理查德所说，使用 psql 的 \d + ，其调用 pg_get_viewdef 在后台。

I created a materialized view in Postgres 9.3 but I have since lost the underlying SELECT query that created it. I would like to DROP the materialized view, rewrite the query to include more data, and then CREATE a materialized view of the same name but with a new underlying query.

解决方案

Just:

SELECT pg_get_viewdef('myview');

from the client of your choice.

e.g. in psql:

test=> CREATE MATERIALIZED VIEW fred AS SELECT x FROM generate_series(1,100) x;
SELECT 100
test=> \a\t
Output format is unaligned.
Showing only tuples.
test=> SELECT pg_get_viewdef('fred');
 SELECT x.x
   FROM generate_series(1, 100) x(x);

This works for normal and materialized views.

Alternately, as Richard says, use psql's \d+, which calls pg_get_viewdef behind the scenes.

这篇关于如何发现我创建的实例化视图的基础查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！