Re: Finding matching words in a word game

On Tue, Mar 5, 2013 at 1:29 AM, Alexander Farber <alexander(dot)farber(at)gmail(dot)com
> wrote:

> Hello,
>
> is there maybe a clever way of finding all possible words
> from a given set of letters by means of PostgreSQL
> (i.e. inside the database vs. scanning all database
> rows by a PHP script, which would take too long) -
> if the dictionary is kept in a simple table like:
>
> create table good_words (
> word varchar(16) primary key,
> stamp timestamp default current_timestamp
> );
>
> I could add a column above, where same letters as in "word"
> would be sorted alphabetically... but then I don't know.
>

Yes, that is how I'd do it. Then you'd just build an index on the sorted
column and select on the sorted letters.

You could do the canonicalization (sorting of the letters) in PHP for every
query (and insert) and pass in that value, and so have the database not
know what the meaning of the new column is.

select word from good_words were sorted_letters=?

Or you could create a function in the database that does the sort, and then
your PHP would not have to pass in already sorted letters.

select word from good_words were sorted_letters=sort_function(?),

and also have triggers automatically compute and store the "sorted_letters"
from "word" upon insert or update.

Cheers,

Jeff