Mercurial > gemma
changeset 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 | 661597546ed9 |
| children | 06d162ac0b9f |
| files | schema/install-db.sh schema/search.sql schema/search_functions.sql |
| diffstat | 3 files changed, 61 insertions(+), 61 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/install-db.sh Wed Jan 16 17:30:13 2019 +0100 +++ b/schema/install-db.sh Wed Jan 16 17:41:05 2019 +0100 @@ -122,7 +122,7 @@ -f "$BASEDIR/isrs.sql" \ -f "$BASEDIR/gemma.sql" \ -f "$BASEDIR/geo_functions.sql" \ - -f "$BASEDIR/search.sql" \ + -f "$BASEDIR/search_functions.sql" \ -f "$BASEDIR/geonames.sql" \ -f "$BASEDIR/manage_users.sql" \ -f "$BASEDIR/auth.sql" \
--- a/schema/search.sql Wed Jan 16 17:30:13 2019 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,60 +0,0 @@ --- This is Free Software under GNU Affero General Public License v >= 3.0 --- without warranty, see README.md and license for details. - --- SPDX-License-Identifier: AGPL-3.0-or-later --- License-Filename: LICENSES/AGPL-3.0.txt - --- Copyright (C) 2018 by via donau --- – Österreichische Wasserstraßen-Gesellschaft mbH --- Software engineering by Intevation GmbH - --- Author(s): --- * Sascha Wilde <wilde@intevation.de> - -CREATE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb - LANGUAGE plpgsql - AS $$ -DECLARE - _result jsonb; -BEGIN - SELECT COALESCE(json_agg(r),'[]') - INTO _result - FROM (SELECT objnam AS name, - ST_AsGeoJSON(ST_Centroid(area))::json AS geom, - 'bottleneck' AS type - FROM waterway.bottlenecks - WHERE objnam ILIKE '%' || search_string || '%' - ORDER BY name) r; - RETURN _result; -END; -$$; - -CREATE FUNCTION search_cities(search_string text) RETURNS jsonb - LANGUAGE plpgsql - AS $$ -DECLARE - _result jsonb; -BEGIN - SELECT COALESCE(json_agg(r),'[]') - INTO _result - FROM (SELECT name || ' (' || country_code || ')' AS name, - ST_AsGeoJSON(location)::json AS geom, - 'city' AS type - FROM waterway.geonames - WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') - AND (name ILIKE '%' || search_string || '%' - OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) - ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], - feature_code::text), - name) r; - RETURN _result; -END; -$$; - -CREATE FUNCTION search_most(search_string text) RETURNS jsonb - LANGUAGE plpgsql - AS $$ -BEGIN - RETURN search_bottlenecks(search_string) || search_cities(search_string); -END; -$$;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/search_functions.sql Wed Jan 16 17:41:05 2019 +0100 @@ -0,0 +1,60 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2018 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde <wilde@intevation.de> + +CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT objnam AS name, + ST_AsGeoJSON(ST_Centroid(area))::json AS geom, + 'bottleneck' AS type + FROM waterway.bottlenecks + WHERE objnam ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT name || ' (' || country_code || ')' AS name, + ST_AsGeoJSON(location)::json AS geom, + 'city' AS type + FROM waterway.geonames + WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') + AND (name ILIKE '%' || search_string || '%' + OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) + ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], + feature_code::text), + name) r; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +BEGIN + RETURN search_bottlenecks(search_string) || search_cities(search_string); +END; +$$;