diff options
| -rw-r--r-- | doc/src/sgml/queries.sgml | 15 | ||||
| -rw-r--r-- | src/test/regress/expected/with.out | 38 | ||||
| -rw-r--r-- | src/test/regress/sql/with.sql | 10 |
3 files changed, 61 insertions, 2 deletions
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index ef9383a2f71..283dd0a73dd 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1,4 +1,4 @@ -<!-- $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> @@ -1681,6 +1681,15 @@ SELECT * FROM search_graph; </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</> @@ -1699,7 +1708,9 @@ SELECT n FROM t LIMIT 100; 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> diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 765910d48ba..13bbb903254 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -499,6 +499,44 @@ select * from search_graph; 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 -- diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 3107cbcb911..d79be72a353 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -272,6 +272,16 @@ with recursive search_graph(f, t, label, path, cycle) as ( ) select * from search_graph; +-- 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; + -- -- test multiple WITH queries -- |