-- Add bottleneck_id column to bottleneck_overview view.

UPDATE sys_admin.published_services
    SET
        view_def = $$
            SELECT
                objnam AS name,
                bn.bottleneck_id,
                ST_Centroid(area) AS point,
                (lower(stretch)).hectometre AS from,
                (upper(stretch)).hectometre AS to,
                sr.current::text,
                responsible_country
            FROM waterway.bottlenecks bn LEFT JOIN (
                SELECT bottleneck_id, max(date_info) AS current
                    FROM waterway.sounding_results
                    GROUP BY bottleneck_id) sr
                    ON sr.bottleneck_id = bn.bottleneck_id
            WHERE bn.validity @> current_timestamp
            ORDER BY objnam
            $$
    WHERE schema = 'waterway' AND name = 'bottleneck_overview';