pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedDocumentation example for RECURISVE WITH isn't what I would expect
2+ messages / 2 participants
[nested] [flat]
* Documentation example for RECURISVE WITH isn't what I would expect
@ 2011-11-25 14:43 Alex Sherwin <alex.sherwin@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Alex Sherwin @ 2011-11-25 14:43 UTC (permalink / raw)
To: pgsql-docs
The 9.1 docs for RECURSIVE WITH:
http://www.postgresql.org/docs/9.1/static/queries-with.html eventually
builds up to this example query:
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Documentation example for RECURISVE WITH isn't what I would expect
@ 2011-11-26 17:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alex Sherwin <alex.sherwin@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2011-11-26 17:10 UTC (permalink / raw)
To: Alex Sherwin <alex.sherwin@gmail.com>; +Cc: pgsql-docs
Alex Sherwin <alex.sherwin@gmail.com> writes:
> The 9.1 docs for RECURSIVE WITH:
> http://www.postgresql.org/docs/9.1/static/queries-with.html eventually
> builds up to this example query:
> [ which is claimed not to work ]
No, the example works as intended for me. The reason "cycle" never gets
set to true in your test case is that your test data contains no loops.
Try something like this instead:
create table graph (id int, link int, data text);
insert into graph values (1, null, 'root');
insert into graph values (2, 1, 'top 1');
insert into graph values (3, 1, 'top 2');
insert into graph values (4, 2, 'child 1');
insert into graph values (5, 3, 'child 2');
insert into graph values (10, 11, 'loop 1');
insert into graph values (11, 10, 'loop 2');
The query as shown in the docs produces:
id | link | data | depth | path | cycle
----+------+---------+-------+------------+-------
1 | | root | 1 | {1} | f
2 | 1 | top 1 | 1 | {2} | f
3 | 1 | top 2 | 1 | {3} | f
4 | 2 | child 1 | 1 | {4} | f
5 | 3 | child 2 | 1 | {5} | f
10 | 11 | loop 1 | 1 | {10} | f
11 | 10 | loop 2 | 1 | {11} | f
1 | | root | 2 | {3,1} | f
1 | | root | 2 | {2,1} | f
2 | 1 | top 1 | 2 | {4,2} | f
3 | 1 | top 2 | 2 | {5,3} | f
10 | 11 | loop 1 | 2 | {11,10} | f
11 | 10 | loop 2 | 2 | {10,11} | f
1 | | root | 3 | {4,2,1} | f
1 | | root | 3 | {5,3,1} | f
10 | 11 | loop 1 | 3 | {10,11,10} | t
11 | 10 | loop 2 | 3 | {11,10,11} | t
(17 rows)
Your proposed query produces:
id | link | data | depth | path | cycle
----+------+---------+-------+------+-------
1 | | root | 1 | {1} | f
2 | 1 | top 1 | 1 | {2} | f
3 | 1 | top 2 | 1 | {3} | f
4 | 2 | child 1 | 1 | {4} | f
5 | 3 | child 2 | 1 | {5} | f
10 | 11 | loop 1 | 1 | {10} | f
11 | 10 | loop 2 | 1 | {11} | f
(7 rows)
which seems pretty broken to me, since it's not showing the paths at
all, much less providing any hint of where the loops are.
You could argue about the usefulness of the specific output from the
sample query --- in particular, it might make more sense to treat the
links as going in the other direction --- but the point here is to
discover all the paths that are traceable through the given set of
links.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-11-26 17:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-25 14:43 Documentation example for RECURISVE WITH isn't what I would expect Alex Sherwin <alex.sherwin@gmail.com>
2011-11-26 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox