Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
- 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 %.%', 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;
- 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;
- if pg_is_in_recovery() then
- return 1;
- else
- 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;
- end if;
- RETURN 1;
- END
- $$ LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION public.refresh_materialized_views(_schema text)
- RETURNS void
- AS
- $BODY$
- DECLARE
- refresh_sql text;
- BEGIN
- WITH matviews AS (
- SELECT t.oid,
- relname AS view_name,
- nspname AS schema_name
- FROM pg_class t
- JOIN pg_catalog.pg_namespace n ON n.oid = t.relnamespace
- WHERE t.relkind = 'm'
- AND nspname NOT LIKE 'pg-%'
- AND nspname = _schema
- ), unique_indexes AS (
- SELECT m.oid,
- view_name,
- schema_name
- FROM pg_class i,
- pg_index ix,
- matviews m
- WHERE ix.indisunique = true
- AND ix.indexrelid = i.oid
- AND ix.indrelid = m.oid
- ), refresh_concurrently AS (
- SELECT 'REFRESH MATERIALIZED VIEW CONCURRENTLY ' || quote_ident(schema_name) || '.' || quote_ident(view_name) AS sql
- FROM unique_indexes
- ), refresh AS (
- SELECT 'REFRESH MATERIALIZED VIEW ' || quote_ident(schema_name) || '.' || quote_ident(view_name) AS sql
- FROM matviews
- WHERE oid != all (SELECT oid FROM unique_indexes)
- ), sql AS (
- SELECT sql FROM refresh_concurrently
- UNION ALL
- SELECT sql FROM refresh
- )
- SELECT string_agg(sql, E';n') || E';n' FROM sql INTO refresh_sql;
- EXECUTE refresh_sql;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE;
- CREATE OR REPLACE VIEW mat_view_dependencies AS
- WITH RECURSIVE s(start_schemaname,start_mvname,schemaname,mvname,relkind,
- mvoid,depth) AS (
- -- List of mat views -- with no dependencies
- SELECT n.nspname AS start_schemaname, c.relname AS start_mvname,
- n.nspname AS schemaname, c.relname AS mvname, c.relkind,
- c.oid AS mvoid, 0 AS depth
- FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid
- WHERE c.relkind='m'
- UNION
- -- Recursively find all things depending on previous level
- SELECT s.start_schemaname, s.start_mvname,
- n.nspname AS schemaname, c.relname AS mvname,
- c.relkind,
- c.oid AS mvoid, depth+1 AS depth
- FROM s
- JOIN pg_depend d ON s.mvoid=d.refobjid
- JOIN pg_rewrite r ON d.objid=r.oid
- JOIN pg_class c ON r.ev_class=c.oid AND (c.relkind IN ('m','v'))
- JOIN pg_namespace n ON n.oid=c.relnamespace
- WHERE s.mvoid <> c.oid -- exclude the current MV which always depends on itself
- )
- SELECT * FROM s;
- CREATE OR REPLACE VIEW mat_view_refresh_order AS
- WITH b AS (
- -- Select the highest depth of each mat view name
- SELECT DISTINCT ON (schemaname,mvname) schemaname, mvname, depth
- FROM mat_view_dependencies
- WHERE relkind='m'
- ORDER BY schemaname, mvname, depth DESC
- )
- -- Reorder appropriately
- SELECT schemaname, mvname, depth AS refresh_order
- FROM b
- ORDER BY depth, schemaname, mvname
- ;
- WITH a AS (
- SELECT 'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || mvname || '";' AS r
- FROM mat_view_refresh_order
- ORDER BY refresh_order
- )
- SELECT string_agg(r,E'n') AS script FROM a gset
- echo :script
- :script
Add Comment
Please, Sign In to add comment