--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1100/01.remove_gauge_validity_refs.sql	Tue Jul 23 13:10:51 2019 +0200
@@ -0,0 +1,110 @@
+-- This is Free Software under GNU Affero General Public License v >= 3.0
+-- without warranty, see README.md and license for details.
+
+-- SPDX-License-Identifier: AGPL-3.0-or-later
+-- License-Filename: LICENSES/AGPL-3.0.txt
+
+-- Copyright (C) 2019 by via donau
+--   – Österreichische Wasserstraßen-Gesellschaft mbH
+-- Software engineering by Intevation GmbH
+
+-- Author(s):
+--  * Sascha Wilde <sascha.wilde@intevation.de>
+
+
+-- Dynamic version of bottlenecks_geoserver view:
+-- Instead of a static reference to a specific gauge data set via
+-- primary key (location, validity) we check for a currently
+-- valid gauge (for the currently valid bottleneck) at executiuon
+-- time.
+CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
+    SELECT
+        b.id,
+        b.bottleneck_id,
+        b.objnam,
+        b.nobjnm,
+        b.stretch,
+        b.area,
+        b.rb,
+        b.lb,
+        b.responsible_country,
+        b.revisiting_time,
+        b.limiting,
+        b.date_info,
+        b.source_organization,
+        g.location AS gauge_isrs_code,
+        g.objname AS gauge_objname,
+        g.reference_water_levels,
+        fal.date_info AS fa_date_info,
+        fal.critical AS fa_critical,
+        g.gm_measuredate,
+        g.gm_waterlevel,
+        g.gm_n_14d,
+        srl.date_max,
+        g.forecast_accuracy_3d,
+        g.forecast_accuracy_1d
+    FROM waterway.bottlenecks b
+        LEFT JOIN waterway.gauges_base_view g
+            ON b.gauge_location = g.location AND g.validity @> current_timestamp
+        LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
+                    bottleneck_id, date_info, critical
+                FROM waterway.fairway_availability
+                ORDER BY bottleneck_id, date_info DESC) AS fal
+            ON b.id = fal.bottleneck_id
+        LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
+                    bottleneck_id, max(date_info) AS date_max
+                FROM waterway.sounding_results
+                GROUP BY bottleneck_id
+                ORDER BY bottleneck_id DESC) AS srl
+            ON b.bottleneck_id = srl.bottleneck_id
+    WHERE b.validity @> current_timestamp;
+
+-- As we resolve the correct gauge data to use on runtime, we drop the
+-- hard reference to the gauges vaidity:
+ALTER TABLE waterway.bottlenecks DROP IF EXISTS gauge_validity;
+
+-- We still want to ensure, that there is at least a valid gauge at
+-- any time of the bottleneck validity.  To ensure this we need a
+-- trigger constraint:
+CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS
+$$
+DECLARE
+    -- FIXME: I'm using text for the isrs code and cast it on demand.
+    -- If someone is able to get it to work with isrs or isrs_base as
+    -- type, feel free to show me how its done...  ;-) [sw]
+    referenced_gauge text;
+    new_validity tstzrange;
+BEGIN
+    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
+        INTO referenced_gauge
+        USING NEW;
+    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
+        INTO new_validity
+        USING NEW;
+    IF EXISTS ( SELECT * FROM waterway.gauges
+                  WHERE location = referenced_gauge::isrs
+                  AND validity && new_validity )
+    THEN
+        RETURN NEW;
+    ELSE
+        RAISE EXCEPTION
+            'new row for relation "%" violates constraint trigger "%"',
+                TG_TABLE_NAME, TG_NAME
+            USING
+                DETAIL = format('No matching gauge %s found.',
+                    isrs_AsText(referenced_gauge::isrs)),
+                ERRCODE = 23505,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                COLUMN = TG_ARGV[0],
+                CONSTRAINT = TG_NAME;
+    END IF;
+END;
+$$
+LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS waterway_bottlenecks_reference_gauge
+    ON waterway.bottlenecks;
+CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
+    AFTER INSERT OR UPDATE OF gauge_location ON waterway.bottlenecks
+    FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')