Mercurial > gemma

annotate schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5736:55892008ec96 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed a bunch of corner cases in WG import.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 29 May 2024 19:02:42 +0200
parents 737d7859dd86
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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$;