Search Postgresql Archives

Re: Finding matching words in a word game

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.farber@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux