Mercurial > gemma
view schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +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$;