Re: WITH RECURSIVE patch V0.1

Hi,

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1
Date: Sun, 18 May 2008 23:22:02 +0200

> But I have a little problem with the output.
> If it's not obvious, here is the query tweaked a little below.
...
> Can we get the rows in tree order, please? I.e. something like this:
>
> ?column?
> ------------------
> 38
> 15
> 10
> 5
> 2
> 3
> 17
> 9
> 8
> 6
> 26
> 13
> 1
> 12
> 18
> 11
> 7
> (17 rows)

No, you can't. However, you can obtain recursive path by using ARRAY
type, as another way. Here is a sample SQL.

WITH RECURSIVE x(level, parent, child, path) AS
(SELECT 1::integer, * , array[child] FROM test_connect_by
WHERE parent IS NULL

UNION ALL

SELECT x.level + 1, base.*, array_append(path, base.child)
FROM test_connect_by AS base, x WHERE base.parent = x.child
)
SELECT path, array_to_string(path, '->') FROM x
WHERE NOT EXISTS (SELECT 1 FROM test_connect_by WHERE parent =
x.child);

path | array_to_string
-------------+-----------------
{18,11} | 18->11
{18,7} | 18->7
{26,13} | 26->13
{26,1} | 26->1
{26,12} | 26->12
{38,6} | 38->6
{38,17,9} | 38->17->9
{38,17,8} | 38->17->8
{38,15,10} | 38->15->10
{38,15,5,2} | 38->15->5->2
{38,15,5,3} | 38->15->5->3
(11 rows)

Regards,
--
Yoshiyuki Asaba
y-asaba(at)sraoss(dot)co(dot)jp