Re: Finding matching words in a word game - Mailing list pgsql-general

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@gmail.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();