Re: WITH RECURSIVE patch V0.1

Hi,

From: Zoltan Boszormenyi <[email protected]>
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
[email protected]

-- 
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches