Mercurial > gemma

diff schema/updates/1005/01.search-only-valid-bottleneck.sql @ 4019:da3ab68875e1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed duplicates for historized bottlenecks in search. Only find bottlenecks currently valid.
author Sascha Wilde <wilde@intevation.de>
date Fri, 19 Jul 2019 17:16:29 +0200
parents
children
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1005/01.search-only-valid-bottleneck.sql	Fri Jul 19 17:16:29 2019 +0200
@@ -0,0 +1,18 @@
+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'::timestamptz
+          ORDER BY name) r;
+  RETURN _result;
+END;
+$$;