Skip to content

Instantly share code, notes, and snippets.

@nickyrabit
Last active April 17, 2023 11:01
Show Gist options
  • Save nickyrabit/22d96cea672c417902711c43b3f733f1 to your computer and use it in GitHub Desktop.
Save nickyrabit/22d96cea672c417902711c43b3f733f1 to your computer and use it in GitHub Desktop.
PostgreSQL Script to Refresh All Materialized Views in your database dynamically
CREATE OR REPLACE FUNCTION refresh_all_materialized_views()
RETURNS VOID AS $$
DECLARE
matview RECORD;
total_views INTEGER;
views_processed INTEGER := 0;
BEGIN
SELECT COUNT(*) INTO total_views FROM pg_matviews;
FOR matview IN (SELECT schemaname, matviewname FROM pg_matviews)
LOOP
EXECUTE 'REFRESH MATERIALIZED VIEW ' || matview.schemaname || '.' || matview.matviewname;
views_processed := views_processed + 1;
RAISE NOTICE 'Refreshing materialized view: % ( % of % )', matview.matviewname, views_processed, total_views;
END LOOP;
END;
$$ LANGUAGE plpgsql;
@nickyrabit
Copy link
Author

run this code in psql

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment