From: | Yoshiyuki Asaba <y-asaba(at)sraoss(dot)co(dot)jp> |
---|---|
To: | zb(at)cybertec(dot)at |
Cc: | ishii(at)postgresql(dot)org, david(at)fetter(dot)org, pgsql-hackers(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org |
Subject: | Re: WITH RECURSIVE patch V0.1 |
Date: | 2008-05-24 09:55:23 |
Message-ID: | 20080524.185523.737004071327933154.y-asaba@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2008-05-24 12:08:51 | Updated patch (Re: WITH RECURSIVE patch V0.1) |
Previous Message | daveg | 2008-05-24 05:25:04 | Re: TODO item: Have psql show current values for a sequence |
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2008-05-24 12:08:51 | Updated patch (Re: WITH RECURSIVE patch V0.1) |
Previous Message | daveg | 2008-05-24 05:25:04 | Re: TODO item: Have psql show current values for a sequence |