From 513504140512d17ddbe0769b2ec3887ee16f7e31 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Wed, 28 Jun 2023 14:27:35 +0200
Subject: [PATCH] replace CASE construct with plpgsql function

---
 lib-sql/functions/ranking.sql            | 23 +++++++++++++++++++++++
 nominatim/api/search/db_search_fields.py |  9 +++++----
 nominatim/version.py                     |  2 +-
 3 files changed, 29 insertions(+), 5 deletions(-)

diff --git a/lib-sql/functions/ranking.sql b/lib-sql/functions/ranking.sql
index af23335a..0b18954c 100644
--- a/lib-sql/functions/ranking.sql
+++ b/lib-sql/functions/ranking.sql
@@ -284,3 +284,26 @@ BEGIN
 END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
+
+
+CREATE OR REPLACE FUNCTION weigh_search(search_vector INT[],
+                                        term_vectors TEXT[],
+                                        weight_vectors FLOAT[],
+                                        def_weight FLOAT)
+  RETURNS FLOAT
+  AS $$
+DECLARE
+  pos INT := 1;
+  terms TEXT;
+BEGIN
+  FOREACH terms IN ARRAY term_vectors
+  LOOP
+    IF search_vector @> terms::INTEGER[] THEN
+      RETURN weight_vectors[pos];
+    END IF;
+    pos := pos + 1;
+  END LOOP;
+  RETURN def_weight;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
diff --git a/nominatim/api/search/db_search_fields.py b/nominatim/api/search/db_search_fields.py
index 325e08df..13f1c56e 100644
--- a/nominatim/api/search/db_search_fields.py
+++ b/nominatim/api/search/db_search_fields.py
@@ -129,10 +129,11 @@ class FieldRanking:
         """
         assert self.rankings
 
-        col = table.c[self.column]
-
-        return sa.case(*((col.contains(r.tokens),r.penalty) for r in self.rankings),
-                       else_=self.default)
+        return sa.func.weigh_search(table.c[self.column],
+                                    [f"{{{','.join((str(s) for s in r.tokens))}}}"
+                                     for r in self.rankings],
+                                    [r.penalty for r in self.rankings],
+                                    self.default)
 
 
 @dataclasses.dataclass
diff --git a/nominatim/version.py b/nominatim/version.py
index 346af5eb..beec32a5 100644
--- a/nominatim/version.py
+++ b/nominatim/version.py
@@ -34,7 +34,7 @@ class NominatimVersion(NamedTuple):
         return f"{self.major}.{self.minor}.{self.patch_level}-{self.db_patch_level}"
 
 
-NOMINATIM_VERSION = NominatimVersion(4, 2, 99, 1)
+NOMINATIM_VERSION = NominatimVersion(4, 2, 99, 2)
 
 POSTGRESQL_REQUIRED_VERSION = (9, 6)
 POSTGIS_REQUIRED_VERSION = (2, 2)
-- 
2.39.5