# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1625265805 -7200
# Node ID 1dd63a2405bcdf466b07c977d38531952579f67c
# Parent  90fc90148ef891bb461f8237412c91a3cf5a5b23
Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.

diff -r 90fc90148ef8 -r 1dd63a2405bc schema/reports.sql
--- a/schema/reports.sql	Fri Jul 02 18:56:59 2021 +0200
+++ b/schema/reports.sql	Sat Jul 03 00:43:25 2021 +0200
@@ -68,18 +68,22 @@
     WHERE bid.objnam = bn.objnam
     GROUP BY bn.cc, d.ym, bid.objnam;
 
-
--- Config update statement
-
--- TODO
---
---  INSERT INTO sys_admin.stats_updates
---    VALUES ('Data quality report',
---            'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats;
---             REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats;');
---
--- THIS DOES _NOT_ WORK!
--- To make this work we need a wrapper function with owner rights for
+-- We need a wrapper procedure with owner rights for
 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
 -- completely replaces the contents of a materialized view. To execute
 -- this command you must be the owner of the materialized view.""
+
+CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats()
+LANGUAGE plpgsql AS $$
+BEGIN
+    EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats';
+    EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats';
+END;
+$$ SECURITY DEFINER;
+
+GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin;
+
+-- Config update statement
+INSERT INTO sys_admin.stats_updates
+    VALUES ('Data quality report',
+            'CALL sys_admin.update_dqr_stats();');
diff -r 90fc90148ef8 -r 1dd63a2405bc schema/updates/1453/01.update_dqr_stats.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1453/01.update_dqr_stats.sql	Sat Jul 03 00:43:25 2021 +0200
@@ -0,0 +1,19 @@
+-- We need a wrapper procedure with owner rights for
+-- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
+-- completely replaces the contents of a materialized view. To execute
+-- this command you must be the owner of the materialized view.""
+
+CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats()
+LANGUAGE plpgsql AS $$
+BEGIN
+    EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats';
+    EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats';
+END;
+$$ SECURITY DEFINER;
+
+GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin;
+
+-- Config update statement
+INSERT INTO sys_admin.stats_updates
+    VALUES ('Data quality report',
+            'CALL sys_admin.update_dqr_stats();');
diff -r 90fc90148ef8 -r 1dd63a2405bc schema/version.sql
--- a/schema/version.sql	Fri Jul 02 18:56:59 2021 +0200
+++ b/schema/version.sql	Sat Jul 03 00:43:25 2021 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1452);
+INSERT INTO gemma_schema_version(version) VALUES (1453);