Hello,
I'm working on setting up a full-text search for some descriptions
stored in my database. I'm running pg 8.4.
From what I can tell, there are two functions. One function,
to_tsquery(), requires that boolean operators (&, |, !) are between
every word. The other function, plainto_tsquery() does not accept
boolean operators, and adds & between every word passed to it.
It seems that these two functions only cover the extremes for
full-text searching. I don't want to force my users to put in & or |
in every single search they do, so to_tsquery() wont work. However, if
they want to use something like "apples AND bananas" it will not work
with plainto_tsquery(). How can I have a "normal" search on my site?
Normal being defined as the following:
-A search for `apples bananas` returns apples OR bananas (with results
containing both ranked high)
-A search for `apples AND bananas` returns results containing both.
-A search for `apple*` returning results that contain apple, apples,
applesauce, etc.
-A search for `"Good Apples"` in quotes returning results that contain
EXACTLY that phrase.
This seems impossible to do with PostgreSQL with the full-text search
functions provided without manually parsing the search string from the
user in my code before querying the DB.
However, even simply parsing the string has it's problems. Simply
replacing spaces with " | " to OR search terms together and replacing
"AND" with & would break the query apart due to formatting issues.
How does everyone implement the typical boolean full-text search in
their database?