I have a working version of a text search engine. I want to make it work
for Postgres (I will be releasing it GPL). It can literally find the
occurrence of a string of words within 5 million records in a few
milliseconds. It is very fast, it works similarly to many web search
engines.
I have tried many approaches to integrate the search system with
Postgres, but I can't find any method that isn't too slow or too
cumbersome.
The best I have been able to come up with is this:
create function textsearch(varchar) returns integer as
'
DECLARE
handle integer;
count integer;
pos integer;
BEGIN
handle = search_exec( \'localhost\', $1);
count = search_count(handle);
for pos in 0 .. count-1 loop
insert into search_result(key, rank)
values (search_key(handle,pos),
search_rank(handle,pos));
end loop;
return search_done(handle);
END;
' language 'plpgsql';
And this is used as:
create temp table search_result (key integer, rank integer);
select textsearch('bla bla');
select field from table where field_key = search_result.key order by
search_result.rank ;
drop table search_result ;
The problems with this are, I can't seem to be able to create a table in
plpgsql. (I read about a patch, but have to find out what version it is
in), so I have to create a table outside the function.
I can only execute one text search, because I can't seem to use the name
of a table that has been passed in to the plpgsql environment, that
would allow multiple searches to be joined. As:
select textsearch(temp_tbl1, 'bla bla');
select textsearch(temp_tbl2, 'foo bar');
select field from table1, table2 where table1.field_key = temp_tbl1.key
and table2.field_key = temp_tbl2.key;
This could be so sweet, but, right now, it is just a disaster and I am
pulling my hair out. Does anyone have any suggestions or tricks that
could make this easier/faster, or is Postgres just unable to do this
sort of thing.
--
http://www.mohawksoft.com
