From: Svenne Krap <svenne.lists@krap.dk>
To: pgsql-sql@postgresql.org
Subject: Multiple recursive part possible?
Date: Sun, 01 May 2011 21:45:24 +0200
Message-ID: <4DBDB854.1010400@krap.dk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: svenne.lists@krap.dk
Subject: Re: Multiple recursive part possible?
In-Reply-To: <4DBDB854.1010400@krap.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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