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
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 |