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