Re: Finding matching words in a word game

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> 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();