Mercurial > gemma

annotate schema/updates/1453/01.update_dqr_stats.sql @ 5510:b7792e8d5c62 deactivate-users

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Client: Improve users-table layout * Make editing/reactivating of users more recognizable by adding a reactive/edit button to the action column, that behaves according to the user's status(active,inactive). * Disable clicking the other cells for editing user.
author Fadi Abbud <fadi.abbud@intevation.de>
date Wed, 29 Sep 2021 17:10:42 +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();');