Re: Recursive queries for network traversal

Hi,

Please note that 'roi_pipes' in the psuedocode in my original post
should actually read 'my_pipes'.

Will

On 8 August 2010 21:16, William Furnass <will@thearete.co.uk> wrote:
> Hi,
>
> I'm very new to PL/pgSQL and am having difficulty in mixing my
> understanding of procedural languages and of set-based operations in
> devising a recursive query for network traversal.  The pseudocode for
> what I wish to do looks like the following:
>
> my_pipes = Pipes[]
>
> func find_all_pipes_upstream(node n)
>  if is_inlet(nodename)
>     return Nil
>  else
>    for p in upstream_pipes:
>      if p in my_pipes:
>        return Nil
>    else
>      roi_pipes.append(p)
>      find_all_pipes_upstream(upstream_node(p))
>
> I've 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
>
> but am struggling to figure out how to manage scoping and return types
> when translating the above pseudocode to pgSQL.  Has anyone any advise
> as how best to go about this?
>
> Thanks in advance.
>
> Cheers,
>
> Will
>
> ----
> Will Furnass
> MSc student (Environmental Management of Urban Land and Water)
> University of Sheffield, UK
>