title: PostgreSQL SQL or PL/pgSQL query for traversing a directed graph and returning all edges found
body: <p>I'm not particularly accustomed to generating complex SQL queries and am having difficulty in mixing my understanding of procedural languages and of set-based operations in
  devising a recursive query for network traversal.  I wish to find the set of edges that lie 'upstream' of a particular node through conducting a depth first search of a directed graph (each node can have more than one upstream edge) and would ideally to implement this in SQL.</p>
  
  <p>The pseudocode for what I wish to do looks like the following:</p>
  
  <pre><code>interesting_pipes = Pipes[]
  
  func find_all_pipes_upstream(node n)
   if is_inlet(nodename)
      return Nil
   else
     for p in upstream_pipes:
       if p in interesting_pipes:
         return Nil
     else
       interesting_pipes.append(p)
       find_all_pipes_upstream(upstream_node(p))
  </code></pre>
  
  <p>I've already written the following functions in pure SQL:</p>
  
  <pre><code>upstream_pipes(node_name varchar) RETURNS SETOF "Pipes"
  upstream_node(pipe_name varchar) RETURNS "Node"
  is_inlet(node_name) RETURNS boolean
  </code></pre>
  
  <p>but am struggling to figure out how to manage scoping and return types
  when translating the above pseudocode to either a PostgreSQL <code>WITH RECURSIVE</code> query or a PL/pgSQL function.  Most of the examples I've seen of <code>WITH RECURSIVE</code> queries have been devised for traversing back through trees where each node has just one parent.  Has anyone any tips/advise as how best to go about this?</p>
  
  <p>Cheers,</p>
  
  <p>Will</p>