Mercurial > gemma

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4019
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2 LANGUAGE plpgsql STABLE PARALLEL SAFE
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3 AS $$
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 DECLARE
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5 _result jsonb;
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6 BEGIN
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
7 SELECT COALESCE(json_agg(r),'[]')
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
8 INTO _result
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9 FROM (SELECT objnam AS name,
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 'bottleneck' AS type
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12 FROM waterway.bottlenecks
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13 WHERE objnam ILIKE '%' || search_string || '%'
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 AND validity @> 'now'::timestamptz
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 ORDER BY name) r;
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16 RETURN _result;
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 END;
da3ab68875e1 Fixed duplicates for historized bottlenecks in search.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 $$;