Mercurial > gemma
annotate schema/updates/1453/01.update_dqr_stats.sql @ 5705:39d91e76c05c sr-v2
Simplify code.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 20 Feb 2024 09:51:20 +0100 |
parents | 1dd63a2405bc |
children |
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();'); |