From: | mlw <markw(at)mohawksoft(dot)com> |
---|---|
To: | Hackers List <pgsql-hackers(at)postgresql(dot)org> |
Subject: | OK, does anyone have any better ideas? |
Date: | 2000-12-08 16:01:11 |
Message-ID: | 3A3105C7.92C2800A@mohawksoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2000-12-08 16:07:07 | Re: pre-beta is slow |
Previous Message | Tom Lane | 2000-12-08 15:47:37 | Re: Indexing for geographic objects? |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Dickhoff | 2000-12-08 16:30:02 | Re[2]: access.mdb import |
Previous Message | Aarmel | 2000-12-08 07:28:42 | ok. |