Mercurial > gemma

annotate schema/updates/1453/01.update_dqr_stats.sql @ 5449:d01c098562d9 uiimprovements

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Bottleneckoverview: Put selector for older scans into date column
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 13 Jul 2021 16:17:01 +0200
parents 1dd63a2405bc
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5391
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 -- We need a wrapper procedure with owner rights for
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 -- completely replaces the contents of a materialized view. To execute
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 -- this command you must be the owner of the materialized view.""
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats()
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 LANGUAGE plpgsql AS $$
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 BEGIN
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats';
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats';
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 END;
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 $$ SECURITY DEFINER;
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14 GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin;
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 -- Config update statement
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 INSERT INTO sys_admin.stats_updates
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 VALUES ('Data quality report',
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 'CALL sys_admin.update_dqr_stats();');