如何一次刷新Postgresql 9.3中的所有实例化视图？ [英] How to refresh all materialized views in Postgresql 9.3 at once?

问题描述

我正在将一堆数据加载到PostgresQL 9.3数据库中，然后我想刷新所有依赖于更新表的实例化视图。有没有一种方法可以自动执行，而不是逐个查看并逐个刷新它们？我知道Oracle可以很轻松地做到这一点，但是在浏览PostgreSQL文档后我什么也没找到。

I am loading a bunch of data into a PostgresQL 9.3 database and then I want to refresh all materialized views that depend on the updated tables. Is there a way to do it automatically instead of going through each view and refreshing them one by one? I know that Oracle can do that rather easily but I did not find anything after combing through PostgreSQL documentation.

推荐答案

看起来像当前版本PostgreSQL（9.3.1）中没有这样的功能，而不得不编写我自己的函数：

Looks like current version of PostgreSQL (9.3.1) does not have such functionality, have had to write my own function instead:

CREATE OR REPLACE FUNCTION RefreshAllMaterializedViews(schema_arg TEXT DEFAULT 'public')
RETURNS INT AS $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE 'Refreshing materialized view in schema %', schema_arg;
    FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = schema_arg 
    LOOP
        RAISE NOTICE 'Refreshing %.%', schema_arg, r.matviewname;
        EXECUTE 'REFRESH MATERIALIZED VIEW ' || schema_arg || '.' || r.matviewname; 
    END LOOP;

    RETURN 1;
END 
$$ LANGUAGE plpgsql;

（在github上： https://github.com/sorokine/RefreshAllMaterializedViews ）

(on github: https://github.com/sorokine/RefreshAllMaterializedViews)

这篇关于如何一次刷新Postgresql 9.3中的所有实例化视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！