Mercurial > gemma

comparison schema/updates/1005/01.search-only-valid-bottleneck.sql @ 4019:da3ab68875e1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed duplicates for historized bottlenecks in search. Only find bottlenecks currently valid.
author Sascha Wilde <wilde@intevation.de>
date Fri, 19 Jul 2019 17:16:29 +0200
parents
children
comparison
equal deleted inserted replaced
4017:639bdb17c3f2 4019:da3ab68875e1
1 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
2 LANGUAGE plpgsql STABLE PARALLEL SAFE
3 AS $$
4 DECLARE
5 _result jsonb;
6 BEGIN
7 SELECT COALESCE(json_agg(r),'[]')
8 INTO _result
9 FROM (SELECT objnam AS name,
10 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
11 'bottleneck' AS type
12 FROM waterway.bottlenecks
13 WHERE objnam ILIKE '%' || search_string || '%'
14 AND validity @> 'now'::timestamptz
15 ORDER BY name) r;
16 RETURN _result;
17 END;
18 $$;