Mercurial > gemma
view schema/updates/1432/01.search_functions-time.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | a6e5bb85f0a7 |
children |
line wrap: on
line source
-- 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,2019,2020 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde <wilde@intevation.de> -- This update adds the new argument "search_time" to -- search_bottlenecks() and the meta function search_most(). DROP FUNCTION search_bottlenecks(text); CREATE OR REPLACE FUNCTION search_bottlenecks( search_string text, search_time timestamp with time zone) 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 @> search_time ORDER BY name) r; RETURN _result; END; $$; DROP FUNCTION search_most(text); CREATE OR REPLACE FUNCTION search_most( search_string text, search_time timestamp with time zone) RETURNS jsonb LANGUAGE plpgsql STABLE PARALLEL SAFE AS $$ BEGIN RETURN search_hectometre(search_string) || search_bottlenecks(search_string, COALESCE(search_time, current_timestamp)) || search_gauges(search_string) || search_sections(search_string) || search_stretches(search_string) || search_cities(search_string); END; $$;