# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1561373254 -7200
# Node ID 71de5ce5a402e77feebab1cb700460ff5f3c3f00
# Parent  6f1d5f310e088af50536d1ac16b1e14c987c68a9
Added search for stretches.

diff -r 6f1d5f310e08 -r 71de5ce5a402 schema/search_functions.sql
--- a/schema/search_functions.sql	Mon Jun 24 12:43:30 2019 +0200
+++ b/schema/search_functions.sql	Mon Jun 24 12:47:34 2019 +0200
@@ -130,6 +130,25 @@
 END;
 $$;
 
+CREATE OR REPLACE FUNCTION search_stretches(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,
+                 'stretch' AS type
+            FROM waterway.stretches
+            WHERE objnam ILIKE '%' || search_string || '%'
+               OR nobjnam ILIKE '%' || search_string || '%'
+          ORDER BY name) r;
+  RETURN _result;
+END;
+$$;
+
 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
   LANGUAGE plpgsql STABLE PARALLEL SAFE
   AS $$
@@ -138,6 +157,7 @@
          || search_bottlenecks(search_string)
          || search_gauges(search_string)
          || search_sections(search_string)
+         || search_stretches(search_string)
          || search_cities(search_string);
 END;
 $$;