Multiple recursive part possible?

From: Svenne Krap <svenne(dot)lists(at)krap(dot)dk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Multiple recursive part possible?
Date: 2011-05-01 19:45:24
Message-ID: 4DBDB854.1010400@krap.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2011-05-01 22:12:29 Re: Multiple recursive part possible?
Previous Message Ricardo Benatti 2011-04-28 21:26:00