Mercurial > gemma

comparison schema/search_functions.sql @ 3732:b1fe61b58048

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Allow to search gauges per ISRS code.
author Sascha Wilde <wilde@intevation.de>
date Mon, 24 Jun 2019 11:22:16 +0200
parents 194feb1a7e37
children 1504856c9378
comparison
equal deleted inserted replaced
3731:0d45a3c34900 3732:b1fe61b58048
55 LANGUAGE plpgsql 55 LANGUAGE plpgsql
56 AS $$ 56 AS $$
57 DECLARE 57 DECLARE
58 _result jsonb; 58 _result jsonb;
59 BEGIN 59 BEGIN
60 SELECT COALESCE(json_agg(r),'[]') 60 IF is_ISRSstring(search_string) THEN
61 INTO _result 61 SELECT COALESCE(json_agg(r),'[]')
62 FROM (SELECT objname AS name, 62 INTO _result
63 ST_AsGeoJSON(geom)::json AS geom, 63 FROM (
64 'gauge' AS type 64 SELECT objname AS name,
65 FROM waterway.gauges 65 ST_AsGeoJSON(geom)::json AS geom,
66 WHERE objname ILIKE '%' || search_string || '%' 66 'gauge' AS type
67 ORDER BY name) r; 67 FROM waterway.gauges
68 WHERE location = isrs_fromText(search_string)
69 ORDER BY name) r;
70 ELSE
71 SELECT COALESCE(json_agg(r),'[]')
72 INTO _result
73 FROM (
74 SELECT objname AS name,
75 ST_AsGeoJSON(geom)::json AS geom,
76 'gauge' AS type
77 FROM waterway.gauges
78 WHERE objname ILIKE '%' || search_string || '%'
79 ORDER BY name) r;
80 END IF;
68 RETURN _result; 81 RETURN _result;
69 END; 82 END;
70 $$; 83 $$;
71 84
72 CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb 85 CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb