Обсуждение: Recursive queries for network traversal

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

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
>