title: PostgreSQL and word games
body: <p>In a word game similar to Ruzzle or Letterpress, where users have to construct words out of a given set of letters:</p>
  
  <p><img src="https://i.stack.imgur.com/n9MN2.jpg" alt="enter image description here"></p>
  
  <p>I keep my dictionary in a simple SQL table:</p>
  
  <pre><code>create table good_words (
          word varchar(16) primary key
  );
  </code></pre>
  
  <p>Since the game duration is very short I do not want to check every entered word by calling a PHP script, which would look that word up in the <code>good_words</code> table.</p>
  
  <p>Instead I'd like to download all possible words by one PHP script call before the round starts - since all letters are known.</p>
  
  <p><strong>My question is:</strong> if there is a nice SQLish way to find such words?</p>
  
  <p>I.e. I could run a longer-taking script once to add a column to <code>good_words</code> table, which would have same letters as in the <code>word</code> columnt, but sorted alphabetically... But I still can't think of a way to match for it given a set of letters.</p>
  
  <p>And doing the word matching inside of a PHP script (vs. inside the database) would probably take too long (because of bandwidth: would have to fetch every row from the database to the PHP script).</p>
  
  <p>Any suggestions or insights please?</p>
  
  <p>Using postgresql-8.4.13 with CentOS Linux 6.3.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Other ideas I have: </p>
  
  <ol>
  <li>Create a constantly running script (cronjob or daemon) which would prefill an SQL table with precompiled letters board and possible words - but still feels like a waste of bandwidth and CPU, I would prefer to solve this inside the database</li>
  <li>Add integer columns <code>a</code>, <code>b</code>, ... , <code>z</code> and whenever I store a <code>word</code> into <code>good_words</code>, store the letter occurences there. I wonder <a href="http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html" rel="noreferrer">if it is possible to create an insert trigger in Pl/PgSQL</a> for that?</li>
  </ol>