WITH RECURSIVE question

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: WITH RECURSIVE question
Date: 2012-07-13 10:20:44
Message-ID: C4DAC901169B624F933534A26ED7DF310861B622@JENMAIL01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

WITH RECURSIVE queries are quite new for me, so I'm not sure if
following is possible

CREATE TEMP TABLE forest (node int,parent int);
INSERT INTO forest VALUES
(1, null),
(2, 1),
(3, 2),
(4, null),
(5, 4),
(6,5);

WITH RECURSIVE struc (pref, id, depth ) AS (
SELECT '', node, 1 from forest where node= 4
UNION ALL
SELECT (case when struc.pref= '' then '\' else struc.pref end )||
'...' ,
node,
struc.depth +1
FROM forest JOIN struc ON parent=struc.id
)
SELECT * FROM struc;

(path,node,depth)
4 1
\... 5 2
\...... 6 3

This is fine as long as I start with a given node (here node= 4).

But How can I retrieve the complete structure in one query ?
do I have to use a procedure for that ?

Something like :

WITH FOR_EACH (node) AS ( SELECT node from forest where parent IS NULL)
SELECT * FROM (
WITH RECURSIVE struc (pref, id, depth ) AS (
SELECT '', node, 1 from forest where node= FOR_EACH.node
UNION ALL
SELECT (case when struc.pref= '' then '\' else struc.pref end )||
'...' ,
node,
struc.depth +1
FROM forest JOIN struc ON parent=struc.id
)
SELECT * FROM struc
)one_tree
;

1 1
\... 2 2
\...... 3 3
4 1
\... 5 2
\...... 6 3

best regards,

Marc Mamin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Leger Bertrand 2012-07-13 10:23:30 Is postgresql 9.1.* supporting plpython with python 2.*
Previous Message Craig Ringer 2012-07-13 08:18:46 Re: hstore for audit logging: Finding differences between two hstore values