Refresh postgres materialized views recursively

inf_refresh_recursive.sql

-- Refresh materialized views recursively

-- DEPENDS:
-- List the tables that a view depends on.
-- Thanks to Dave: http://stackoverflow.com/questions/4229468/getting-a-list-of-tables-that-a-view-table-depends-on-in-postgresql
create or replace function inf_view_dependencies(v text)
returns table (kind text, name text) as $$
SELECT     cl_d.relkind::text as kind
        ,  cl_d.relname::text AS name
FROM pg_rewrite AS r
JOIN pg_class AS cl_r ON r.ev_class=cl_r.oid
JOIN pg_depend AS d ON r.oid=d.objid
JOIN pg_class AS cl_d ON d.refobjid=cl_d.oid
WHERE cl_d.relkind IN ('r','v','m') AND cl_r.relname=$1 and cl_d.relname!=$1
GROUP BY cl_d.relname, cl_d.relkind
ORDER BY cl_d.relname;
$$ language SQL;

-- Refresh materialized views recursively:
create or replace function inf_refresh_recursive(tab text) returns TABLE(action text, kind text, object text) AS
$$
DECLARE tab2 text;
BEGIN
        FOR tab2 in select name from inf_view_dependencies(tab)
        LOOP
                RETURN QUERY SELECT 'explore'::text, relkind::text, relname::text from pg_class where relname=tab;
                RETURN QUERY SELECT * from  inf_refresh_recursive(tab2);
        END LOOP;
        FOR tab2 in select relname from pg_class where relname=tab and relkind='m'
        LOOP
                RETURN QUERY SELECT 'refresh'::text, relkind::text, relname::text from pg_class where relname=tab;
                EXECUTE 'REFRESH MATERIALIZED VIEW '||tab2;
        END LOOP;
END
$$
LANGUAGE plpgsql;

-- Refresh materialized views recursively:
create or replace function inf_refresh_recursive_concurrently(tab text) returns TABLE(action text, kind text, object text) AS
$$
DECLARE tab2 text;
BEGIN
        FOR tab2 in select name from inf_view_dependencies(tab)
        LOOP
                RETURN QUERY SELECT 'explore'::text, relkind::text, relname::text from pg_class where relname=tab;
                RETURN QUERY SELECT * from  inf_refresh_recursive_concurrently(tab2);
        END LOOP;
        FOR tab2 in select relname from pg_class where relname=tab and relkind='m'
        LOOP
                RETURN QUERY SELECT 'refresh'::text, relkind::text, relname::text from pg_class where relname=tab;
                EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY '||tab2;
        END LOOP;
END
$$
LANGUAGE plpgsql;