title: Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables
body: <p>I have a table that contains a field of comma separated strings: </p>
  
  <pre><code>ID | fruits
  -----------
  1  | cherry,apple,grape 
  2  | apple,orange,peach 
  </code></pre>
  
  <p>I want to create a normalized version of the table, like this: </p>
  
  <pre><code>ID | fruits
  -----------
  1  | cherry 
  1  | apple 
  1  | grape
  2  | apple 
  2  | orange 
  2  | peach 
  </code></pre>
  
  <p>The postgresql 8.4 documentation describes a regexp_split_to_table function that can turn a single table:  </p>
  
  <pre><code>SELECT foo 
  FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog',E'\\s+') AS
  foo;
  </code></pre>
  
  <p>which gives you this: </p>
  
  <pre><code>  foo   
  --------
    the    
    quick  
    brown  
    fox    
    jumped 
    over   
    the    
    lazy   
    dog    
   (9 rows)
  </code></pre>
  
  <p>But that is just for a single field. What I want to do is some kind UNION applied to all the tables generated by splitting each field. Thank you. </p>