title: SQL - postgres - shortest path in graph - recursion
body: <p>I have a table which contains the edges from node <em>x</em> to node <em>y</em> in a graph.</p>
  
  <pre><code>n1 | n2
  -------
  a  | a
  a  | b
  a  | c
  b  | b
  b  | d
  b  | c
  d  | e
  </code></pre>
  
  <p>I would like to create a (materialized) view which denotes the shortest number of nodes/hops a path contains to reach from <em>x</em> to node <em>y</em>:</p>
  
  <pre><code>n1 | n2 | c
  -----------
  a  | a  | 0
  a  | b  | 1
  a  | c  | 1
  a  | d  | 2
  a  | e  | 3
  b  | b  | 0
  b  | d  | 1
  b  | c  | 1
  b  | e  | 2
  d  | e  | 1
  </code></pre>
  
  <p>How should I model my tables and views to facilitate this? I guess I need some kind of recursion, but I believe that is pretty difficult to accomplish in SQL. I would like to avoid that, for example, the clients need to fire 10 queries if the path happens to contain 10 nodes/hops.</p>