title: String matching in insert trigger - how to use in conditionals to return NULL?
body: <p>In PostgreSQL 8.4.13 database with UTF8 encoding I keep a (non-English) dictionary in this table:</p>
  
  <pre><code>create table good_words (
          word varchar(64) primary key
  );
  </code></pre>
  
  <p>And a list of wrong (but often suggested or mistyped) words in:</p>
  
  <pre><code>create table bad_words (
          word varchar(64) primary key
  );
  </code></pre>
  
  <p>For the former table I have an <a href="http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html" rel="nofollow">BEFORE INSERT trigger</a> which I am trying to extend:</p>
  
  <pre><code>create or replace function keep_clean() returns trigger as $body$
          begin
                  new.word := upper(new.word);
  
                  perform true
                          from bad_words
                          where word = new.word;
  
                  if found then
                          return null;
                  end if;
  
                  -- forbid words with [XYZ] at beginning and Z at the end
                  -- forbid words with LLL unless it is KLLL or MLLL
  
                  return new;
          end;
  $body$ language plpgsql;
  
  create trigger count_letters
          BEFORE INSERT on good_words
          for each row execute procedure keep_clean();
  </code></pre>
  
  <p><strong>My problem is:</strong> I am trying to add 3 rules (returning <code>NULL</code>) to my trigger:</p>
  
  <ol>
  <li>Words starting with X, Y or Z letter are forbidden</li>
  <li>Words ending with Z letter are forbidden</li>
  <li>Words with 3 same letters in a row are very rare and are only allowed if they are <code>like '%KLLL%' or like '%MLLL%'</code></li>
  </ol>
  
  <p>As Perl programmer I know regexes well, but my problem is the Pl/PgSQL part - how to perform the regex matching in that language, do I always have to use <code>SELECT INTO</code> or <code>PERFORM</code>? Or can I use <code>:=</code> operator here or maybe even perform a string matching inside the <code>IF</code> statement?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>After Craig's explanation (thank you!) I have prepared <a href="http://sqlfiddle.com/#!11/0e834" rel="nofollow">the following SQL Fiddle</a>, but it still has 2 problems:</p>
  
  <pre><code>create table good_words (
          word varchar(64) primary key
  );
  
  create or replace function keep_clean() returns trigger as $body$
          begin
                  new.word := upper(new.word);
  
                  /* next line does not compile? */
                  IF new.word !~ '^[\x0410-\x042F]{2,}$' THEN
                      RAISE EXCEPTION 'Not an uppercased Russian word in UTF8';
                  END IF;
  
                  IF new.word ~ '^[ЪЫЬ]' OR new.word ~ 'Ъ$' THEN
                      return NULL;
                  END IF;
  
                  /* does not return NULL for 'ошибббка'? */
                  IF new.word ~ '(.)\1\1' AND new.word NOT LIKE '%ШЕЕЕ%' AND new.word NOT LIKE '%ЗМЕЕЕ%' THEN
                      return NULL;
                  END IF;
  
                  return new;
          end;
  $body$ language plpgsql;
  </code></pre>
  
  <p>It shouldn't take the 1st two words in UTF8 encoding here, but it does:</p>
  
  <pre><code>insert into good_words (word)
    values
      ('abcde'),          /* bad word: non-russian */
      ('ошибббка'),       /* bad word: 3 letters in a row */
      ('длинношеее'),
      ('проверка')
  ;
  
  select * from good_words;
  </code></pre>
  
  <p><strong>UPDATE 2:</strong> Trigger works now, thank you: <a href="http://sqlfiddle.com/#!11/98403/1" rel="nofollow">http://sqlfiddle.com/#!11/98403/1</a></p>