X-Git-Url: https://git.openstreetmap.org./osqa.git/blobdiff_plain/2591b1770a0d108069a8b1ffee485ae02f796263..15eabbf86190e4bc8ae9dc747d853b0300976b12:/forum_modules/pgfulltext/pg_fts_install.sql

diff --git a/forum_modules/pgfulltext/pg_fts_install.sql b/forum_modules/pgfulltext/pg_fts_install.sql
index bba7eef..c046870 100644
--- a/forum_modules/pgfulltext/pg_fts_install.sql
+++ b/forum_modules/pgfulltext/pg_fts_install.sql
@@ -53,15 +53,16 @@ begin
         return new;
     END IF;
 
-    doc :=
-      setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') ||
-      setweight(to_tsvector('english', coalesce(new.title,'')), 'B') ||
-      setweight(to_tsvector('english', coalesce(new.body,'')), 'C');
+    SELECT
+      setweight(to_tsvector('english', coalesce(tagnames,'')), 'A') ||
+      setweight(to_tsvector('english', coalesce(title,'')), 'B') ||
+      setweight(to_tsvector('english', coalesce(body,'')), 'C') INTO doc
+      FROM forum_node WHERE id = root_id;
 
-    SELECT count(*)::int INTO rcount FROM forum_node WHERE abs_parent_id = root_id AND deleted_id IS NULL;
+    SELECT count(*)::int INTO rcount FROM forum_node WHERE abs_parent_id = root_id AND (NOT state_string LIKE '%%deleted%%');
 
     IF rcount > 0 THEN
-       FOR cv in SELECT setweight(to_tsvector('english', coalesce(body,'')), 'C') FROM forum_node WHERE abs_parent_id = root_id  AND deleted_id IS NULL LOOP
+       FOR cv in SELECT setweight(to_tsvector('english', coalesce(body,'')), 'C') FROM forum_node WHERE abs_parent_id = root_id  AND (NOT state_string LIKE '%%deleted%%') LOOP
            doc :=(doc || cv);
        END LOOP;
      END IF;
@@ -104,4 +105,25 @@ select case when public.doc_table_exists()=0 then public.build_doc_table()end;
 drop function build_doc_table();
 drop function doc_table_exists();
 
-UPDATE forum_noderevision SET id = id WHERE TRUE;
+CREATE OR REPLACE FUNCTION rank_exact_matches(rank float) RETURNS float AS $$
+begin
+	IF rank = 0 THEN
+		return 1;
+	ELSE
+		return rank;
+	END IF;
+
+end
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION public.rebuild_index() RETURNS VOID as $$
+	DECLARE
+		r integer;
+	BEGIN
+		FOR r IN SELECT active_revision_id FROM forum_node WHERE node_type = 'question' LOOP
+			UPDATE forum_noderevision SET id = id WHERE id = r;
+		END LOOP;
+	END
+$$ LANGUAGE 'plpgsql';
+
+SELECT rebuild_index();