Mercurial > gemma
view schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5650:cb3c4ba0f052 DQR adjustments
Merged default branch
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 29 Jun 2023 13:18:54 +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$;