Mercurial > gemma

comparison schema/search_functions.sql @ 2452:0f1a915344c5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added the ability to search for gauges.
author Sascha Wilde <wilde@intevation.de>
date Fri, 01 Mar 2019 17:06:25 +0100
parents b9c59050014a
children 194feb1a7e37
comparison
equal deleted inserted replaced
2451:960550ccca55 2452:0f1a915344c5
49 name) r; 49 name) r;
50 RETURN _result; 50 RETURN _result;
51 END; 51 END;
52 $$; 52 $$;
53 53
54 CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb
55 LANGUAGE plpgsql
56 AS $$
57 DECLARE
58 _result jsonb;
59 BEGIN
60 SELECT COALESCE(json_agg(r),'[]')
61 INTO _result
62 FROM (SELECT objname AS name,
63 ST_AsGeoJSON(geom)::json AS geom,
64 'gauge' AS type
65 FROM waterway.gauges
66 WHERE objname ILIKE '%' || search_string || '%'
67 ORDER BY name) r;
68 RETURN _result;
69 END;
70 $$;
71
54 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb 72 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
55 LANGUAGE plpgsql 73 LANGUAGE plpgsql
56 AS $$ 74 AS $$
57 BEGIN 75 BEGIN
58 RETURN search_bottlenecks(search_string) || search_cities(search_string); 76 RETURN search_bottlenecks(search_string)
77 || search_gauges(search_string)
78 || search_cities(search_string);
59 END; 79 END;
60 $$; 80 $$;