--
-- category_synonym__search/2
--
create or replace function category_synonym__search(
  character varying,
  character varying
) returns int4 as $$

-- return id for search string
declare
	p_search_text	alias for $1;
	p_locale	alias for $2;
	v_search_text	varchar(200);
	v_query_id	integer;
	v_len		integer;
	v_i		integer;
begin
	-- check if search text already exists
	select	query_id into v_query_id
	from	category_search
	where	search_text = p_search_text
	and 	locale = p_locale;

	-- simply update old search data if already exists
	if (v_query_id is not null) then
		update	category_search
		set	queried_count = queried_count + 1,
			last_queried = date('now')
		where	query_id = v_query_id;
		return (v_query_id);
	end if;

	-- get new search query id
	v_query_id := nextval ('category_search_id_seq');

	-- convert string to uppercase and substitute special chars
	v_search_text := category_synonym__convert_string (p_search_text);

	-- insert search data
	insert into category_search (query_id, search_text, locale, queried_count, last_queried)
	values (v_query_id, p_search_text, p_locale, 1, date('now'));

	-- build search index
	v_len := length (v_search_text) - 2;
	v_i := 1;
	while (v_i <= v_len) loop
		insert into category_search_index 
		values (v_query_id, substring (v_search_text, v_i , 3));
		v_i := v_i + 1;
	end loop;

	-- build search result
	insert into category_search_results
	select	v_query_id, s.synonym_id, 
		category_synonym__get_similarity (v_len, length (s.name) - 2, count(*))
	from	category_search_index si, 
		category_synonym_index i,
		category_synonyms s
	where	si.query_id = v_query_id
	and	si.trigram = i.trigram
	and	s.synonym_id = i.synonym_id
	and	s.locale = p_locale
	group by s.synonym_id, s.name;

	return (v_query_id);
end;$$ language plpgsql;