# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1561369580 -7200
# Node ID 1504856c9378d12d203d612b3e05b414049894b8
# Parent  b1fe61b580487358ecb3e64641954952d186e265
Moved rhm search to db.

This unifies the approach to feature search.  Now everything is
searched via the search_most() PLPGSQL function.

diff -r b1fe61b58048 -r 1504856c9378 pkg/controllers/search.go
--- a/pkg/controllers/search.go	Mon Jun 24 11:22:16 2019 +0200
+++ b/pkg/controllers/search.go	Mon Jun 24 11:46:20 2019 +0200
@@ -18,20 +18,12 @@
 	"database/sql"
 	"net/http"
 	"regexp"
-	"strconv"
 	"strings"
 
 	"gemma.intevation.de/gemma/pkg/models"
 )
 
 const (
-	searchHectometreSQL = `SELECT COALESCE(json_agg(r),'[]')
-FROM (SELECT (location_code).hectometre || ' rhm' AS name,
-			replace(concat(location_code), ',','') AS locationcode,
-             ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
-      FROM waterway.distance_marks_virtual
-      WHERE (location_code).hectometre = $1) r`
-
 	searchMostSQL = `SELECT search_most($1)::text`
 
 	listBottlenecksSQL = `
@@ -66,37 +58,11 @@
 	}
 
 	var result string
-
-	m := rkmRegex.FindStringSubmatch(s.SearchString)
-	if len(m) != 0 {
-		// Handle search for river kilometre:
-		var hectometre int
-		if hectometre, err = strconv.Atoi(m[1]); err != nil {
-			return
-		}
-
-		hectometre *= 10
-		if m[3] != "" {
-			var h int
-			if h, err = strconv.Atoi(m[3]); err != nil {
-				return
-			}
-			hectometre += h
-		}
-
-		err = db.QueryRowContext(
-			req.Context(),
-			searchHectometreSQL,
-			hectometre,
-		).Scan(&result)
-	} else {
-		// Hande search for bottlencks:
-		err = db.QueryRowContext(
-			req.Context(),
-			searchMostSQL,
-			s.SearchString,
-		).Scan(&result)
-	}
+	err = db.QueryRowContext(
+		req.Context(),
+		searchMostSQL,
+		s.SearchString,
+	).Scan(&result)
 
 	if err != nil {
 		return
diff -r b1fe61b58048 -r 1504856c9378 schema/search_functions.sql
--- a/schema/search_functions.sql	Mon Jun 24 11:22:16 2019 +0200
+++ b/schema/search_functions.sql	Mon Jun 24 11:46:20 2019 +0200
@@ -4,13 +4,34 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018 by via donau
+-- Copyright (C) 2018,2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
 -- Author(s):
 --  * Sascha Wilde <wilde@intevation.de>
 
+CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb
+  LANGUAGE plpgsql
+  AS $$
+DECLARE
+  _result jsonb;
+BEGIN
+  IF search_string SIMILAR TO '[0-9]+' THEN
+    SELECT COALESCE(json_agg(r),'[]')
+      INTO _result
+      FROM (SELECT (location_code).hectometre || ' rhm' AS name,
+  			replace(concat(location_code), ',','') AS locationcode,
+               ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
+        FROM waterway.distance_marks_virtual
+        WHERE (location_code).hectometre = search_string::int) r;
+  ELSE
+    _result='[]';
+  END IF;
+  RETURN _result;
+END;
+$$;
+
 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
   LANGUAGE plpgsql
   AS $$
@@ -105,7 +126,8 @@
   LANGUAGE plpgsql
   AS $$
 BEGIN
-  RETURN search_bottlenecks(search_string)
+  RETURN search_hectometre(search_string)
+         || search_bottlenecks(search_string)
          || search_gauges(search_string)
          || search_sections(search_string)
          || search_cities(search_string);