Message-ID: <4DBDB854.1010400@krap.dk>
Date: Sun, 01 May 2011 21:45:24 +0200
From: Svenne Krap <svenne.lists@krap.dk>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110430 Lightning/1.0b3pre Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Multiple recursive part possible?
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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