view schema/updates/1006/01.fix-search-only-valid-bottleneck.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 b10f210af325
children
line wrap: on
line source

CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
  LANGUAGE plpgsql STABLE PARALLEL SAFE
  AS $$
DECLARE
  _result jsonb;
BEGIN
  SELECT COALESCE(json_agg(r),'[]')
    INTO _result
    FROM (SELECT objnam AS name,
                 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
                 'bottleneck' AS type
            FROM waterway.bottlenecks
            WHERE objnam ILIKE '%' || search_string || '%'
              AND validity @> now()
          ORDER BY name) r;
  RETURN _result;
END;
$$;