title: Postgresql Group by array elements in common
body: <p>I have a table like this:</p>
  
  <pre><code>CREATE TABLE preferences (name varchar, preferences varchar[]);
  INSERT INTO preferences (name, preferences) 
  VALUES 
      ('John','{pizza, spaghetti}'), 
      ('Charlie','{spaghetti, rice}'), 
      ('Lucy','{rice, potatoes}'), 
      ('Beth','{bread, cheese}'), 
      ('Trudy','{rice, milk}');
  </code></pre>
  
  <p>So from the table</p>
  
  <pre><code>John      {pizza, spaghetti}
  Charlie   {spaghetti, rice}
  Lucy      {rice, potatoes}
  Beth      {bread, cheese}
  Trudy     {rice, milk}
  </code></pre>
  
  <p>I would like group all rows that have elements in common (even if it is through other people).
  So in this case I would like to end up with:</p>
  
  <pre><code>{John,Charlie,Lucy,Trudy}     {pizza,spaghetti,rice,potatoes,milk}
  {Beth}                        {bread, cheese}
  </code></pre>
  
  <p>because Johns preferences intersect with those of Charlie, and those of Charlie intersect with those of Lucy and with those of Trudy.</p>
  
  <p>I already haven an array_intersection function like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION array_intersection(anyarray, anyarray)
    RETURNS anyarray
    language sql
  as $FUNCTION$
      SELECT ARRAY(
          SELECT UNNEST($1)
          INTERSECT
          SELECT UNNEST($2)
      );
  $FUNCTION$;
  </code></pre>
  
  <p>and know the array_agg function to aggregate arrays, but how to turn those into a grouping like I want is the step I am missing.</p>