Mercurial > gemma
view schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5098:52aac557cbd7 queued-stage-done
Merged default intp 'queued-stage-done' branch.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 24 Mar 2020 13:07:24 +0100 |
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$;