pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Philippe Lang <philippe.lang@attiksystem.ch>
To: pgsql-sql@postgresql.org
Subject: Re: WITH RECURSION output ordering with trees
Date: Fri, 10 Jul 2009 13:09:13 +0200
Message-ID: <E6A0649F1FBFA3408A37F505400E7AC215CE68@email.attiksystem.ch> (raw )
References: <E6A0649F1FBFA3408A37F505400E7AC215CE67@email.attiksystem.ch >
pgsql-sql-owner@postgresql.org wrote:
> Hi,
>
> I'm playing with the new "WITH RECURSIVE" feature of 8.4. I'm trying
> to figure out how to use it with trees.
>
> Here is the test code I use:
>
> ---------------------------------------------------------
> --DROP TABLE recursion;
>
> CREATE TABLE recursion
> (
> id serial,
> lookup varchar(16),
> parent_id integer,
> primary key(id),
> foreign key(parent_id) references recursion(id) );
>
> INSERT INTO recursion VALUES(1, 'a1', NULL);
> INSERT INTO recursion VALUES(2, 'b11', 1);
> INSERT INTO recursion VALUES(645, 'c111', 2);
> INSERT INTO recursion VALUES(823, 'c112', 2);
> INSERT INTO recursion VALUES(243, 'c113', 2);
> INSERT INTO recursion VALUES(6, 'b12', 1);
> INSERT INTO recursion VALUES(845, 'c121', 6);
> INSERT INTO recursion VALUES(583, 'c122', 6);
> INSERT INTO recursion VALUES(9, 'b13', 1);
> INSERT INTO recursion VALUES(10, 'c131', 9);
>
> WITH RECURSIVE parse_tree (depth, id, lookup, parent_id) AS (
> SELECT
> 0,
> parent.id,
> parent.lookup,
> parent.parent_id
> FROM recursion AS parent
> WHERE parent_id IS NULL
>
> UNION ALL
>
> SELECT
> parent.depth + 1,
> child.id,
> child.lookup,
> child.parent_id
> FROM parse_tree parent, recursion AS child
> WHERE child.parent_id = parent.id
> )
>
> SELECT * FROM parse_tree;
> ---------------------------------------------------------
>
> Here is the result:
>
> depth | id | lookup | parent_id
> -------+-----+--------+-----------
> 0 | 1 | a1 |
> 1 | 2 | b11 | 1
> 1 | 6 | b12 | 1
> 1 | 9 | b13 | 1
> 2 | 645 | c111 | 2
> 2 | 823 | c112 | 2
> 2 | 243 | c113 | 2
> 2 | 845 | c121 | 6
> 2 | 583 | c122 | 6
> 2 | 10 | c131 | 9
>
> I'd like to perform a real recursion, and show the tree structure in
> a more appopriate way, like this:
>
> depth | id | lookup | parent_id
> -------+-----+--------+-----------
> 0 | 1 | a1 |
> 1 | 2 | b11 | 1
> 2 | 645 | c111 | 2
> 2 | 823 | c112 | 2
> 2 | 243 | c113 | 2
> 1 | 6 | b12 | 1
> 2 | 845 | c121 | 6
> 2 | 583 | c122 | 6
> 1 | 9 | b13 | 1
> 2 | 10 | c131 | 9
>
> Any idea how to do that? (without trying to sort on the lookup
> column, whose values can be random outside this test)
Hi again,
I reply to my own post: I found a way to parse the tree with the help of
the tablefunc contrib package:
---------------------------------------------------------
SELECT
t.depth,
t.id,
r.lookup,
t.parent_id
FROM connectby('recursion', 'id', 'parent_id', 'lookup', '1', 0)
AS t(id integer, parent_id integer, depth integer, o integer)
INNER JOIN recursion AS r
ON t.id = r.id
---------------------------------------------------------
depth | id | lookup | parent_id
-------+-----+--------+-----------
0 | 1 | a1 |
1 | 2 | b11 | 1
2 | 645 | c111 | 2
2 | 823 | c112 | 2
2 | 243 | c113 | 2
1 | 6 | b12 | 1
2 | 845 | c121 | 6
2 | 583 | c122 | 6
1 | 9 | b13 | 1
2 | 10 | c131 | 9
I guess this is hard to achieve with a "WITH RECURSIVE" call.
So my question is now: is the inclusion of "START WITH... CONNECT BY"
planned for Postgresql? I read a patch had been developed for Postgresql
8.3:
http://www.postgresql-support.de/blog/blog_hans.html
Best regards,
Philippe Lang
view thread (6+ messages) latest in thread
Message-ID: <E6A0649F1FBFA3408A37F505400E7AC215CE68@email.attiksystem.ch>
Permalink: ../E6A0649F1FBFA3408A37F505400E7AC215CE68@email.attiksystem.ch/
Also on: postgresql.org/message-id/E6A0649F1FBFA3408A37F505400E7AC215CE68@email.attiksystem.ch
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: philippe.lang@attiksystem.ch
Subject: Re: WITH RECURSION output ordering with trees
In-Reply-To: <E6A0649F1FBFA3408A37F505400E7AC215CE68@email.attiksystem.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox