Mercurial > gemma
view schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5420:851c14d57680 marking-single-beam
Merged default into marking-single-beam branch.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 08 Jul 2021 00:14:58 +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$;