<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.49 2008/10/14 00:12:44 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.50 2008/10/14 00:41:34 tgl Exp $ -->

<chapter id="queries">

<title>Queries</title>

</para>

</tip>

<tip>

<para>

The recursive query evaluation algorithm produces its output in

breadth-first search order. You can display the results in depth-first

search order by making the outer query <literal>ORDER BY</> a

<quote>path</> column constructed in this way.

</para>

</tip>

<para>

A helpful trick for testing queries

when you are not certain if they might loop is to place a <literal>LIMIT</>

This works because <productname>PostgreSQL</productname>'s implementation

evaluates only as many rows of a <literal>WITH</> query as are actually

fetched by the parent query. Using this trick in production is not

recommended, because other systems might work differently.

recommended, because other systems might work differently. Also, it

usually won't work if you make the outer query sort the recursive query's

results or join them to some other table.

</para>

<para>

2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f

(25 rows)

-- ordering by the path column has same effect as SEARCH DEPTH FIRST

with recursive search_graph(f, t, label, path, cycle) as (

select *, array[row(g.f, g.t)], false from graph g

union all

select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)

from graph g, search_graph sg

where g.f = sg.t and not cycle

)

select * from search_graph order by path;

f | t | label | path | cycle

---+---+------------+-------------------------------------------+-------

1 | 2 | arc 1 -> 2 | {"(1,2)"} | f

2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f

1 | 3 | arc 1 -> 3 | {"(1,3)"} | f

1 | 4 | arc 1 -> 4 | {"(1,4)"} | f

4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f

5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f

1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f

2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f

1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f

1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t

2 | 3 | arc 2 -> 3 | {"(2,3)"} | f

4 | 5 | arc 4 -> 5 | {"(4,5)"} | f

5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f

1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f

2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f

1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f

1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f

4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t

5 | 1 | arc 5 -> 1 | {"(5,1)"} | f

1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f

2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f

1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f

1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f

4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f

5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t

(25 rows)

--

-- test multiple WITH queries

--

)

select*from search_graph;

with recursive search_graph(f, t, label,path, cycle)as (

select*, array[row(g.f,g.t)], falsefrom graph g

select g.*,path|| row(g.f,g.t), row(g.f,g.t)= any(path)

from graph g, search_graph sg

whereg.f=sg.tand not cycle

)

select*from search_graphorder bypath;