forked from sorokine/RefreshAllMaterializedViews
-
Notifications
You must be signed in to change notification settings - Fork 1
/
RefreshAllMaterializedViews.sql
22 lines (21 loc) · 976 Bytes
/
RefreshAllMaterializedViews.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--
-- function to refresh all materialized views in all or a specified schema, optionally concurrently
--
CREATE OR REPLACE FUNCTION RefreshAllMaterializedViews(_schema TEXT DEFAULT '*', _concurrently BOOLEAN DEFAULT false)
RETURNS INT AS $$
DECLARE
r RECORD;
BEGIN
RAISE NOTICE 'Refreshing materialized view(s) in % %', CASE WHEN _schema = '*' THEN 'all schemas' ELSE 'schema "'|| _schema || '"' END, CASE WHEN _concurrently THEN 'concurrently' ELSE '' END;
IF pg_is_in_recovery() THEN
RETURN 0;
ELSE
FOR r IN SELECT schemaname, matviewname FROM pg_matviews WHERE schemaname = _schema OR _schema = '*'
LOOP
RAISE NOTICE 'Refreshing materialized view "%"."%"', r.schemaname, r.matviewname;
EXECUTE 'REFRESH MATERIALIZED VIEW ' || CASE WHEN _concurrently THEN 'CONCURRENTLY ' ELSE '' END || '"' || r.schemaname || '"."' || r.matviewname || '"';
END LOOP;
END IF;
RETURN 1;
END
$$ LANGUAGE plpgsql;