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:
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
