--
-- 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;