Mercurial > gemma

comparison schema/search_functions.sql @ 3741:71de5ce5a402

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added search for stretches.
author Sascha Wilde <wilde@intevation.de>
date Mon, 24 Jun 2019 12:47:34 +0200
parents 6f1d5f310e08
children 78ba9b2574ff
comparison
equal deleted inserted replaced
3740:6f1d5f310e08 3741:71de5ce5a402
128 ORDER BY name) r; 128 ORDER BY name) r;
129 RETURN _result; 129 RETURN _result;
130 END; 130 END;
131 $$; 131 $$;
132 132
133 CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb
134 LANGUAGE plpgsql STABLE PARALLEL SAFE
135 AS $$
136 DECLARE
137 _result jsonb;
138 BEGIN
139 SELECT COALESCE(json_agg(r),'[]')
140 INTO _result
141 FROM (SELECT objnam AS name,
142 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
143 'stretch' AS type
144 FROM waterway.stretches
145 WHERE objnam ILIKE '%' || search_string || '%'
146 OR nobjnam ILIKE '%' || search_string || '%'
147 ORDER BY name) r;
148 RETURN _result;
149 END;
150 $$;
151
133 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb 152 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
134 LANGUAGE plpgsql STABLE PARALLEL SAFE 153 LANGUAGE plpgsql STABLE PARALLEL SAFE
135 AS $$ 154 AS $$
136 BEGIN 155 BEGIN
137 RETURN search_hectometre(search_string) 156 RETURN search_hectometre(search_string)
138 || search_bottlenecks(search_string) 157 || search_bottlenecks(search_string)
139 || search_gauges(search_string) 158 || search_gauges(search_string)
140 || search_sections(search_string) 159 || search_sections(search_string)
160 || search_stretches(search_string)
141 || search_cities(search_string); 161 || search_cities(search_string);
142 END; 162 END;
143 $$; 163 $$;