This is how I have been doing searches for my own use:
SELECT * FROM table WHERE lower(textfield) ~ ('***=' || lower($1)) ORDER BY timestamp DESC LIMIT 100
That is, it case-insensitively finds any exact instance of "test string" inside the textfield column and sorts by the timestamp column. This makes sense for me, most of the time, but others expect very different behaviour. And sometimes, I admit I also would like it to be more lenient or more flexible.
People in general expect to be able to search for "croccodhiles" and find records containing "crocodile" and "crocs" and whatnot, sorted by perceived "relevance" or "similarity".
Is there a nice and robust query I can use to accomplish this, in the vein of what I already have, and which orders the results by "how similar they are" rather than by the timestamp? Something like:
SELECT * FROM table WHERE textfield SMARTMATCHES $1 ORDER BY RELEVANCE DESC LIMIT 100
I need for this to be done with only the built-in PostgreSQL features, as dealing with extensions is a nightmare to me.