Mercurial > gemma
changeset 4874:7cbe5d32a614
Added search for bottleneck_id.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Fri, 31 Jan 2020 17:11:30 +0100 |
parents | 8a4c98b80fbd |
children | 6237e6165041 |
files | schema/search_functions.sql schema/updates/1316/01.search_for_bottleneck_id.sql schema/version.sql |
diffstat | 3 files changed, 25 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/search_functions.sql Fri Jan 24 17:33:12 2020 +0100 +++ b/schema/search_functions.sql Fri Jan 31 17:11:30 2020 +0100 @@ -52,9 +52,11 @@ INTO _result FROM (SELECT objnam AS name, ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, - 'bottleneck' AS type + 'bottleneck' AS type, + bottleneck_id AS location FROM waterway.bottlenecks - WHERE objnam ILIKE '%' || search_string || '%' + WHERE (objnam ILIKE '%' || search_string || '%' + OR bottleneck_id ILIKE '%' || search_string || '%') AND validity @> now() ORDER BY name) r; RETURN _result;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1316/01.search_for_bottleneck_id.sql Fri Jan 31 17:11:30 2020 +0100 @@ -0,0 +1,20 @@ +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, + bottleneck_id AS location + FROM waterway.bottlenecks + WHERE (objnam ILIKE '%' || search_string || '%' + OR bottleneck_id ILIKE '%' || search_string || '%') + AND validity @> now() + ORDER BY name) r; + RETURN _result; +END; +$$;