How to refresh all materialized views in Postgresql 9.3 at once?

srk picture srk · Nov 14, 2013 · Viewed 10.9k times · Source

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.

Answer

srk picture srk · Nov 15, 2013

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;

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