OK, does anyone have any better ideas?

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