X-Git-Url: https://git.openstreetmap.org./osqa.git/blobdiff_plain/b484fb008ae574dd4ef10a12d4e530ab96743acc..fda16cc8044e41ca8bd76e40c725f8f21173841e:/forum_modules/pgfulltext/pg_fts_install.sql?ds=sidebyside

diff --git a/forum_modules/pgfulltext/pg_fts_install.sql b/forum_modules/pgfulltext/pg_fts_install.sql
index 9ac5db9..9a6b60d 100644
--- a/forum_modules/pgfulltext/pg_fts_install.sql
+++ b/forum_modules/pgfulltext/pg_fts_install.sql
@@ -29,24 +29,19 @@
      SELECT active_revision_id INTO rev_id FROM forum_node WHERE id = node_id;
      SELECT tsv INTO v FROM forum_noderevision WHERE id = rev_id;
 
-    SELECT  count(*) INTO child_count FROM forum_node WHERE abs_parent_id = node_id AND NOT deleted;
+    SELECT  count(*) INTO child_count FROM forum_node WHERE abs_parent_id = node_id AND deleted_id IS NULL;
 
     IF child_count > 0 THEN
-       FOR r in SELECT * FROM forum_node WHERE abs_parent_id = node_id  AND NOT deleted LOOP
+       FOR r in SELECT * FROM forum_node WHERE abs_parent_id = node_id  AND deleted_id IS NULL LOOP
            SELECT tsv INTO cv FROM forum_noderevision WHERE id = r.active_revision_id;
            v :=(v || cv);
        END LOOP;
      END IF;
-     RAISE NOTICE '%', v;
 
      RETURN ts_rank_cd(v, plainto_tsquery('english', srch), 32);
   end
   $$ LANGUAGE plpgsql;
 
-select node_ranking(50, 'free');
-
-
-
   CREATE OR REPLACE FUNCTION set_node_tsv() RETURNS TRIGGER AS $$
   begin
       new.tsv :=
@@ -58,19 +53,20 @@ select node_ranking(50, 'free');
   end
   $$ LANGUAGE plpgsql;
 
-  CREATE OR REPLACE FUNCTION public.create_tsv_noderevision_column ()
-      RETURNS TEXT
-      AS $$
+  CREATE OR REPLACE FUNCTION public.create_tsv_noderevision_column () RETURNS TEXT AS $$
+  begin
           ALTER TABLE forum_noderevision ADD COLUMN tsv tsvector;
 
+          DROP TRIGGER IF EXISTS tsvectorupdate ON forum_noderevision;
+
           CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
 	        ON forum_noderevision FOR EACH ROW EXECUTE PROCEDURE set_node_tsv();
 
 	      CREATE INDEX noderevision_tsv ON forum_noderevision USING gin(tsv);
 
-          SELECT 'tsv column created'::TEXT;
-      $$
-  LANGUAGE 'sql';
+          RETURN 'tsv column created'::TEXT;
+  end
+  $$ LANGUAGE plpgsql;
 
   SELECT CASE WHEN
      (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_noderevision') AND attname = 'tsv')
