Mercurial > gemma
view schema/updates/1437/01.historicise_fairway_dimensions.sql @ 5279:a17c2a0b8e44
client: use current time in the review layer request
* avoid using value from time slider in the request for review layer
author | Fadi Abbud <fadi.abbud@intevation.de> |
---|---|
date | Thu, 11 Jun 2020 15:50:11 +0200 |
parents | 1cb5fca140e2 |
children |
line wrap: on
line source
-- Drop trigger first to avoid execution during following operations DROP TRIGGER fairway_dimensions_area_unique ON waterway.fairway_dimensions; ALTER TABLE waterway.fairway_dimensions ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) CHECK (NOT isempty(validity)); -- Assume existing entries have been valid since last accepted import WITH imps AS ( SELECT changed, CAST(summary AS jsonb)->'fd-area' AS fd_area FROM import.imports WHERE kind = 'fd' AND state = 'accepted' ) UPDATE waterway.fairway_dimensions fd SET validity = tstzrange( COALESCE( (SELECT max(changed) FROM imps WHERE fd.id IN( SELECT id FROM jsonb_to_recordset(fd_area) AS fd_area (id bigint, lat numeric, lon numeric))), current_timestamp), NULL); CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique AFTER INSERT OR UPDATE OF area, validity, staging_done ON waterway.fairway_dimensions FOR EACH ROW EXECUTE FUNCTION prevent_st_equals( 'area', 'validity WITH &&', 'staging_done'); CREATE INDEX fairway_dimensions_validity ON waterway.fairway_dimensions USING GiST (validity);