pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple recursive part possible?
3+ messages / 2 participants
[nested] [flat]
* Multiple recursive part possible?
@ 2011-05-01 19:45 Svenne Krap <svenne.lists@krap.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Svenne Krap @ 2011-05-01 19:45 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple recursive part possible?
@ 2011-05-01 22:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Svenne Krap <svenne.lists@krap.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2011-05-01 22:12 UTC (permalink / raw)
To: Svenne Krap <svenne.lists@krap.dk>; +Cc: pgsql-sql
Svenne Krap <svenne.lists@krap.dk> writes:
> The two recursive parts seems to do the right thing each on its own, but
> together i get an error...
> with recursive downpath as (
> [ something ]
> ),
> with recursive uppath as (
> [ something ]
> )
> select ...
Leave out the second "with recursive". WITH introduces a list of
name-AS-subselect clauses, not just one.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple recursive part possible?
@ 2011-05-02 03:18 Svenne Krap <svenne.lists@krap.dk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Svenne Krap @ 2011-05-02 03:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 02-05-2011 00:12, Tom Lane wrote:
> Leave out the second "with recursive". WITH introduces a list of
> name-AS-subselect clauses, not just one.
>
Thanks :)
Svenne
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-02 03:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-01 19:45 Multiple recursive part possible? Svenne Krap <svenne.lists@krap.dk>
2011-05-01 22:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-02 03:18 ` Svenne Krap <svenne.lists@krap.dk>
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