PostgreSQL SQL or PL / pgSQL query to move a directed graph and return all edges found

Question

I'm not particularly used to generating complex SQL queries, and it's hard for me to mix my understanding of procedural languages ​​and set-based operations into developing a recursive query to bypass the network. I want to find a set of edges that lie "upstream" of a particular node by conducting a first search for the depth of the oriented graph (each node can have more than one ascending edge) and should ideally implement this in SQL.

The pseudocode for what I want to do is as follows:

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))

I have already written the following functions in pure SQL:

upstream_pipes(node_name varchar) RETURNS SETOF "Pipes"
upstream_node(pipe_name varchar) RETURNS "Node"
is_inlet(node_name) RETURNS boolean

, PostgreSQL WITH RECURSIVE PL/pgSQL. , WITH RECURSIVE, , node . - /, ?

,

Answer 1

:

http://www.postgresql.org/docs/8.4/static/queries-with.html

, . "" , UNION ALL UNION . , , . , :

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;