title: Find cluster given node in PostgreSQL
body: <p>I am representing a graph in Postgres 9.1 (happens to be bidirectional and cyclic):</p>
  
  <pre><code>CREATE TABLE nodes (
    id          SERIAL PRIMARY KEY,
    name        text
  );
  
  CREATE TABLE edges (
    id          SERIAL PRIMARY KEY,
    node1_id    int REFERENCES nodes(id),
    node2_id    int REFERENCES nodes(id)
  );
  </code></pre>
  
  <p>Given a particular node ID, want to retrieve all other nodes in that cluster.  I started with the "Paths from a single node" example <a href="http://explainextended.com/2010/12/24/postgresql-parametrizing-a-recursive-cte/" rel="nofollow">here</a>, and this is where I got:</p>
  
  <pre><code>WITH RECURSIVE search_graph(id, path) AS (
      SELECT id, ARRAY[id]
      FROM nodes
    UNION
      SELECT e.node2_id, sg.path || e.node2_id
      FROM search_graph sg
      JOIN edges e
      ON e.node1_id = sg.id
  )
  -- find all nodes connected to node 3
  SELECT DISTINCT id FROM search_graph WHERE path @&gt; ARRAY[3];
  </code></pre>
  
  <p>I can't figure out a) if there is a simpler way to write this since I don't care about collecting the full <code>path</code>, and b) how to make it traverse in both directions (<code>node1</code>-><code>node2</code> and <code>node2</code>-><code>node1</code> for each edge).  Shedding any light on a good approach would be appreciated.  Thanks!</p>