Mercurial > gemma
comparison schema/search_functions.sql @ 1833:b9c59050014a
Make search functions self-replacing and indicate that in filename
All other files containing only functions already were named
*_functions.sql.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 16 Jan 2019 17:41:05 +0100 |
parents | |
children | 0f1a915344c5 |
comparison
equal
deleted
inserted
replaced
1832:661597546ed9 | 1833:b9c59050014a |
---|---|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 | |
2 -- without warranty, see README.md and license for details. | |
3 | |
4 -- SPDX-License-Identifier: AGPL-3.0-or-later | |
5 -- License-Filename: LICENSES/AGPL-3.0.txt | |
6 | |
7 -- Copyright (C) 2018 by via donau | |
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH | |
9 -- Software engineering by Intevation GmbH | |
10 | |
11 -- Author(s): | |
12 -- * Sascha Wilde <wilde@intevation.de> | |
13 | |
14 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb | |
15 LANGUAGE plpgsql | |
16 AS $$ | |
17 DECLARE | |
18 _result jsonb; | |
19 BEGIN | |
20 SELECT COALESCE(json_agg(r),'[]') | |
21 INTO _result | |
22 FROM (SELECT objnam AS name, | |
23 ST_AsGeoJSON(ST_Centroid(area))::json AS geom, | |
24 'bottleneck' AS type | |
25 FROM waterway.bottlenecks | |
26 WHERE objnam ILIKE '%' || search_string || '%' | |
27 ORDER BY name) r; | |
28 RETURN _result; | |
29 END; | |
30 $$; | |
31 | |
32 CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb | |
33 LANGUAGE plpgsql | |
34 AS $$ | |
35 DECLARE | |
36 _result jsonb; | |
37 BEGIN | |
38 SELECT COALESCE(json_agg(r),'[]') | |
39 INTO _result | |
40 FROM (SELECT name || ' (' || country_code || ')' AS name, | |
41 ST_AsGeoJSON(location)::json AS geom, | |
42 'city' AS type | |
43 FROM waterway.geonames | |
44 WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') | |
45 AND (name ILIKE '%' || search_string || '%' | |
46 OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) | |
47 ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], | |
48 feature_code::text), | |
49 name) r; | |
50 RETURN _result; | |
51 END; | |
52 $$; | |
53 | |
54 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb | |
55 LANGUAGE plpgsql | |
56 AS $$ | |
57 BEGIN | |
58 RETURN search_bottlenecks(search_string) || search_cities(search_string); | |
59 END; | |
60 $$; |