Hi. I just tried to make a query that traverses a tree upwards to locate the root and then downwards to locate all branches on PGSQL 9.0.
The two recursive parts seems to do the right thing each on its own, but together i get an error... ERROR: syntax error at or near "with recursive" LINE 6: with recursive uppath as ( The query in question is with recursive downpath as ( select id , id as bottom, 0 as level from organisation union all select o.id,bottom, level + 1 from organisation o inner join downpath as dp on (dp.id = o.parent) ), with recursive uppath as ( select id, id as top, parent, 0::integer as level from organisation union all select o.id, p.top, o.parent, level + 1 as level from organisation o inner join uppath p on (p.parent = o.id) ) select * from downpath where bottom = (select id from uppath where top = 9 and parent is null); It seems like multiple recursive parts are disallowed (or unhandled). Is there any way to do that query, or do I have to move it out from the database? Or perhaps wrap the "uppath" part in a function (i would prefer not to)? There doesn't seem to be any mentioning of only one recursive part in the docs (at least, I can't find it). I know that is is going to be an expensive query, but I really need all of the tree from the root (parent is null) and downwards... and there is only going to be a couple of 10.000 rows ever (much fewer the first years) Svenne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql