Mercurial > gemma

comparison schema/search_functions.sql @ 3735:96230eadf96e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added some optimizer hints to search functions.
author Sascha Wilde <wilde@intevation.de>
date Mon, 24 Jun 2019 11:49:08 +0200
parents 1504856c9378
children 4cc3b28fd87b
comparison
equal deleted inserted replaced
3734:1504856c9378 3735:96230eadf96e
10 10
11 -- Author(s): 11 -- Author(s):
12 -- * Sascha Wilde <wilde@intevation.de> 12 -- * Sascha Wilde <wilde@intevation.de>
13 13
14 CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb 14 CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb
15 LANGUAGE plpgsql 15 LANGUAGE plpgsql STABLE PARALLEL SAFE
16 AS $$ 16 AS $$
17 DECLARE 17 DECLARE
18 _result jsonb; 18 _result jsonb;
19 BEGIN 19 BEGIN
20 IF search_string SIMILAR TO '[0-9]+' THEN 20 IF search_string SIMILAR TO '[0-9]+' THEN
31 RETURN _result; 31 RETURN _result;
32 END; 32 END;
33 $$; 33 $$;
34 34
35 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb 35 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
36 LANGUAGE plpgsql 36 LANGUAGE plpgsql STABLE PARALLEL SAFE
37 AS $$ 37 AS $$
38 DECLARE 38 DECLARE
39 _result jsonb; 39 _result jsonb;
40 BEGIN 40 BEGIN
41 SELECT COALESCE(json_agg(r),'[]') 41 SELECT COALESCE(json_agg(r),'[]')
49 RETURN _result; 49 RETURN _result;
50 END; 50 END;
51 $$; 51 $$;
52 52
53 CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb 53 CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb
54 LANGUAGE plpgsql 54 LANGUAGE plpgsql STABLE PARALLEL SAFE
55 AS $$ 55 AS $$
56 DECLARE 56 DECLARE
57 _result jsonb; 57 _result jsonb;
58 BEGIN 58 BEGIN
59 SELECT COALESCE(json_agg(r),'[]') 59 SELECT COALESCE(json_agg(r),'[]')
71 RETURN _result; 71 RETURN _result;
72 END; 72 END;
73 $$; 73 $$;
74 74
75 CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb 75 CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb
76 LANGUAGE plpgsql 76 LANGUAGE plpgsql STABLE PARALLEL SAFE
77 AS $$ 77 AS $$
78 DECLARE 78 DECLARE
79 _result jsonb; 79 _result jsonb;
80 BEGIN 80 BEGIN
81 IF is_ISRSstring(search_string) THEN 81 IF is_ISRSstring(search_string) THEN
102 RETURN _result; 102 RETURN _result;
103 END; 103 END;
104 $$; 104 $$;
105 105
106 CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb 106 CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb
107 LANGUAGE plpgsql 107 LANGUAGE plpgsql STABLE PARALLEL SAFE
108 AS $$ 108 AS $$
109 DECLARE 109 DECLARE
110 _result jsonb; 110 _result jsonb;
111 BEGIN 111 BEGIN
112 SELECT COALESCE(json_agg(r),'[]') 112 SELECT COALESCE(json_agg(r),'[]')
121 RETURN _result; 121 RETURN _result;
122 END; 122 END;
123 $$; 123 $$;
124 124
125 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb 125 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
126 LANGUAGE plpgsql 126 LANGUAGE plpgsql STABLE PARALLEL SAFE
127 AS $$ 127 AS $$
128 BEGIN 128 BEGIN
129 RETURN search_hectometre(search_string) 129 RETURN search_hectometre(search_string)
130 || search_bottlenecks(search_string) 130 || search_bottlenecks(search_string)
131 || search_gauges(search_string) 131 || search_gauges(search_string)