Mercurial > gemma
annotate schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5454:b4216db975e3 uiimprovements
fix layout for contextbox content.
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Wed, 14 Jul 2021 16:21:27 +0200 |
parents | 737d7859dd86 |
children |
rev | line source |
---|---|
5030
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 -- Cannot alter type of a column used in a policy definition |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 DROP POLICY responsibility_area_insert ON waterway.fairway_dimensions; |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 DROP POLICY responsibility_area_select ON waterway.fairway_dimensions; |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 DROP POLICY responsibility_area_update ON waterway.fairway_dimensions; |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 DROP POLICY responsibility_area_delete ON waterway.fairway_dimensions; |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 ALTER TABLE waterway.fairway_dimensions |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 ALTER area TYPE geography(MULTIPOLYGON, 4326) |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 USING ST_Multi(CAST(area AS geometry)); |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 -- Re-create policies |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 DO LANGUAGE plpgsql |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 $do$ |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 DECLARE |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 the_table CONSTANT varchar = 'fairway_dimensions'; |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 condition CONSTANT text = $$ |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a))) |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 FROM users.current_user_area_utm() AS a (a)) |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 $$; |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 BEGIN |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 EXECUTE format($$ |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 CREATE POLICY responsibility_area_insert ON waterway.%I |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 FOR INSERT TO waterway_admin |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 WITH CHECK (%s) |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 $$, the_table, condition); |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 EXECUTE format($$ |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 CREATE POLICY responsibility_area_select ON waterway.%I |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 FOR SELECT TO waterway_admin |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 USING (staging_done OR %s) |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 $$, the_table, condition); |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 EXECUTE format($$ |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 CREATE POLICY responsibility_area_update ON waterway.%I |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 FOR UPDATE TO waterway_admin |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 USING (%s) |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 $$, the_table, condition); |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 EXECUTE format($$ |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 CREATE POLICY responsibility_area_delete ON waterway.%I |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 FOR DELETE TO waterway_admin |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 USING (%s) |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 $$, the_table, condition); |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 END; |
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 $do$; |