-- -- category_synonym__reindex/3 -- create or replace function category_synonym__reindex( integer, character varying, character varying ) returns int4 as $$ -- build search index for synonym declare p_synonym_id alias for $1; p_name alias for $2; p_locale alias for $3; v_name varchar(200); v_len integer; v_i integer; begin -- delete old search results for this synonym delete from category_search_results where synonym_id = p_synonym_id; -- delete old synonym index for this synonym delete from category_synonym_index where synonym_id = p_synonym_id; -- convert string to uppercase and substitute special chars v_name := category_synonym__convert_string (p_name); -- rebuild synonym index v_len := length (v_name) - 2; v_i := 1; while (v_i <= v_len) loop insert into category_synonym_index values (p_synonym_id, substring (v_name, v_i , 3)); v_i := v_i + 1; end loop; -- rebuild search results insert into category_search_results select s.query_id, p_synonym_id, category_synonym__get_similarity (v_len, length (s.search_text) - 2, count(*)) from category_search_index si, category_synonym_index i, category_search s where i.synonym_id = p_synonym_id and si.trigram = i.trigram and si.query_id = s.query_id and s.locale = p_locale group by s.query_id, s.search_text; return (1); end;$$ language plpgsql;