Mercurial > gemma
view schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5736:55892008ec96 default tip
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 |
line wrap: on
line source
-- Cannot alter type of a column used in a policy definition DROP POLICY responsibility_area_insert ON waterway.fairway_dimensions; DROP POLICY responsibility_area_select ON waterway.fairway_dimensions; DROP POLICY responsibility_area_update ON waterway.fairway_dimensions; DROP POLICY responsibility_area_delete ON waterway.fairway_dimensions; ALTER TABLE waterway.fairway_dimensions ALTER area TYPE geography(MULTIPOLYGON, 4326) USING ST_Multi(CAST(area AS geometry)); -- Re-create policies DO LANGUAGE plpgsql $do$ DECLARE the_table CONSTANT varchar = 'fairway_dimensions'; condition CONSTANT text = $$ (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a))) FROM users.current_user_area_utm() AS a (a)) $$; BEGIN EXECUTE format($$ CREATE POLICY responsibility_area_insert ON waterway.%I FOR INSERT TO waterway_admin WITH CHECK (%s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY responsibility_area_select ON waterway.%I FOR SELECT TO waterway_admin USING (staging_done OR %s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY responsibility_area_update ON waterway.%I FOR UPDATE TO waterway_admin USING (%s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY responsibility_area_delete ON waterway.%I FOR DELETE TO waterway_admin USING (%s) $$, the_table, condition); END; $do$;