Re: Finding matching words in a word game

Hi,

I think you can make another table:

Word, letter, count (word, letter - pk)

In good_words add column sorted_letters.

Now we can make a view based on that two tables:

Word, letter, count, sorted_letters

Now we need two immutable functions:

1. For given word returns sorted_letters "word"
2. For given word returns set of our_view

Trigger on insert good_words will set sorted_letters and insert rows in
word_letter_count table.... Using above functions...

And now we have the letters: "ogdssoedillrthyhtmkjilsdaio"

We can now say

Select distinct our_view.word from second_function(the_letters) f

Join our_view using(letter)

Where f.sorted_letters like our_view.sorted_letters || '%' and
our_view.count <= f.count

Now to improve performance i think would be good to put index on (letter,
count) and maybe second part in where move to join part... But it would
depend on explain analyze...

Kind regards,
Misa

On Tuesday, March 5, 2013, Alexander Farber wrote:

> I've come up with the following INSERT trigger,
> if you have any improvement suggestions,
> please let me know (and also I wonder
> what to do with non-english language here,
> where I can't name columns "a", "b", etc.) -
>
> On Tue, Mar 5, 2013 at 10:59 AM, Alexander Farber
> <alexander(dot)farber(at)gmail(dot)com <javascript:;>> wrote:
> >> http://stackoverflow.com/questions/15220072/postgresql-and-word-games
>
> create table good_words (
> word varchar(16) primary key,
> a integer not null default 0,
> b integer not null default 0,
> c integer not null default 0,
> d integer not null default 0,
> e integer not null default 0,
> /* ...skipped 20 letters... */
> z integer not null default 0
> );
>
> CREATE or REPLACE FUNCTION count_letters() RETURNS trigger AS $BODY$
> BEGIN
> SELECT into NEW.a LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'a',
> ''));
> SELECT into NEW.b LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'b',
> ''));
> SELECT into NEW.c LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'c',
> ''));
> SELECT into NEW.d LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'd',
> ''));
> /* ...skipped 20 letters... */
> SELECT into NEW.z LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'z',
> ''));
> RETURN NEW;
> END;
> $BODY$ LANGUAGE plpgsql;
>
> CREATE TRIGGER count_letters BEFORE INSERT OR UPDATE ON good_words
> FOR EACH ROW EXECUTE PROCEDURE count_letters();
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org<javascript:;>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>