Mercurial > gemma
diff schema/updates/1453/01.update_dqr_stats.sql @ 5391:1dd63a2405bc extented-report
Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Sat, 03 Jul 2021 00:43:25 +0200 |
| parents | |
| children |
line wrap: on
line diff
--- /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();');