Mercurial > gemma
annotate schema/updates/1432/01.search_functions-time.sql @ 5307:4ba3fda13c50 zpg-exception
Fixed typo in migration script.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 28 May 2021 11:17:12 +0200 |
parents | a6e5bb85f0a7 |
children |
rev | line source |
---|---|
5130
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 -- Copyright (C) 2018,2019,2020 by via donau |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 -- Author(s): |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 -- * Sascha Wilde <wilde@intevation.de> |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 -- This update adds the new argument "search_time" to |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 -- search_bottlenecks() and the meta function search_most(). |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 DROP FUNCTION search_bottlenecks(text); |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 CREATE OR REPLACE FUNCTION search_bottlenecks( |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 search_string text, |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 search_time timestamp with time zone) |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 RETURNS jsonb |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 LANGUAGE plpgsql STABLE PARALLEL SAFE |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 AS $$ |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 DECLARE |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 _result jsonb; |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 BEGIN |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 SELECT COALESCE(json_agg(r),'[]') |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 INTO _result |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 FROM (SELECT objnam AS name, |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 'bottleneck' AS type, |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 bottleneck_id AS location |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 FROM waterway.bottlenecks |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 WHERE (objnam ILIKE '%' || search_string || '%' |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 OR bottleneck_id ILIKE '%' || search_string || '%') |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 AND validity @> search_time |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 ORDER BY name) r; |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 RETURN _result; |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 END; |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 $$; |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 DROP FUNCTION search_most(text); |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 CREATE OR REPLACE FUNCTION search_most( |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 search_string text, |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 search_time timestamp with time zone) |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 RETURNS jsonb |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 LANGUAGE plpgsql STABLE PARALLEL SAFE |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 AS $$ |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 BEGIN |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 RETURN search_hectometre(search_string) |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 || search_bottlenecks(search_string, |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 COALESCE(search_time, current_timestamp)) |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 || search_gauges(search_string) |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 || search_sections(search_string) |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 || search_stretches(search_string) |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 || search_cities(search_string); |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 END; |
a6e5bb85f0a7
Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 $$; |