Revision 7327
Added by Aaron Marcuse-Kubitza almost 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 962 | 962 |
LANGUAGE plpgsql |
| 963 | 963 |
AS $$ |
| 964 | 964 |
DECLARE |
| 965 |
-- Save dependent objects |
|
| 966 |
analytical_aggregate_view_def text := |
|
| 967 |
'CREATE OR REPLACE VIEW analytical_aggregate_view AS ' |
|
| 968 |
||pg_get_viewdef('analytical_aggregate_view');
|
|
| 969 |
provider_count_view_def text := |
|
| 970 |
'CREATE OR REPLACE VIEW provider_count_view AS ' |
|
| 971 |
||pg_get_viewdef('provider_count_view');
|
|
| 965 |
analytical_aggregate_view_def text := NULL; |
|
| 966 |
provider_count_view_def text := NULL; |
|
| 972 | 967 |
BEGIN |
| 973 |
-- Drop dependent objects |
|
| 974 |
DROP VIEW provider_count_view; |
|
| 975 |
DROP VIEW analytical_aggregate_view; |
|
| 968 |
-- Save and drop dependent objects |
|
| 969 |
BEGIN |
|
| 970 |
analytical_aggregate_view_def := |
|
| 971 |
'CREATE OR REPLACE VIEW analytical_aggregate_view AS ' |
|
| 972 |
||pg_get_viewdef('analytical_aggregate_view');
|
|
| 973 |
DROP VIEW analytical_aggregate_view; |
|
| 974 |
EXCEPTION |
|
| 975 |
WHEN undefined_table THEN NULL; |
|
| 976 |
END; |
|
| 977 |
BEGIN |
|
| 978 |
provider_count_view_def := |
|
| 979 |
'CREATE OR REPLACE VIEW provider_count_view AS ' |
|
| 980 |
||pg_get_viewdef('provider_count_view');
|
|
| 981 |
DROP VIEW provider_count_view; |
|
| 982 |
EXCEPTION |
|
| 983 |
WHEN undefined_table THEN NULL; |
|
| 984 |
END; |
|
| 976 | 985 |
|
| 977 | 986 |
DROP TABLE IF EXISTS analytical_stem; |
| 978 | 987 |
CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view LIMIT 0; |
| ... | ... | |
| 990 | 999 |
CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "dateCollected");
|
| 991 | 1000 |
|
| 992 | 1001 |
-- Re-create dependent objects |
| 993 |
EXECUTE analytical_aggregate_view_def; |
|
| 994 |
GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read; |
|
| 995 |
EXECUTE provider_count_view_def; |
|
| 996 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
|
| 997 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
|
| 1002 |
IF analytical_aggregate_view_def IS NOT NULL THEN |
|
| 1003 |
EXECUTE analytical_aggregate_view_def; |
|
| 1004 |
GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read; |
|
| 1005 |
END IF; |
|
| 1006 |
IF provider_count_view_def IS NOT NULL THEN |
|
| 1007 |
EXECUTE provider_count_view_def; |
|
| 1008 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
|
| 1009 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
|
| 1010 |
END IF; |
|
| 998 | 1011 |
END; |
| 999 | 1012 |
$$; |
| 1000 | 1013 |
|
Also available in: Unified diff
schemas/vegbien.sql: sync_analytical_stem_to_view(): Support running the function when dependent views do not exist. This allows using the sync function when changing column names of the analytical_stem_view, which sometimes requires manually dropping and re-creating the analytical_aggregate_view.