How to navigate tree without CONNECT BY? - Mailing list pgsql-general
From | D. Dante Lorenso |
---|---|
Subject | How to navigate tree without CONNECT BY? |
Date | |
Msg-id | 3FE1A8FB.1030101@lorenso.com Whole thread Raw |
Responses |
Re: How to navigate tree without CONNECT BY?
(Joe Conway <mail@joeconway.com>)
Re: How to navigate tree without CONNECT BY? (Andrei Ivanov <andrei.ivanov@ines.ro>) Re: How to navigate tree without CONNECT BY? (CoL <col@mportal.hu>) |
List | pgsql-general |
I have a simple table that I'd like to query to pull out a heirarchy from a tree relationship. What is the best way to do this without a 'CONNECT BY' clause like Oracle has? Example mytable +----------+-----------+ | child_id | parent_id | +----------+-----------+ | 1 | NULL | | 2 | NULL | | 3 | 1 | | 4 | 1 | | 5 | 2 | | 6 | 4 | | 7 | 4 | | 8 | 7 | | 9 | 3 | | 10 | 9 | +----------+-----------+ I want to be able to select the child_id, parent_id, and the up-stream heirarchy level when starting at a given child... In Oracle you'd use a statement like SELECT * FROM account START WITH child_id = 10 CONNECT BY PRIOR parent_id = child_id; (* note: may not be exactly correct *) I was thinking that PL/PGSQL could return a set using a function like 'get_tree_relation(child_id INTEGER)' Example 1: SELECT * FROM get_tree_relation(10) ORDER BY level ASC; +----------+-----------+-------+ | child_id | parent_id | level | +----------+-----------+-------+ | 10 | 9 | 1 | | 9 | 3 | 2 | | 3 | 1 | 3 | | 1 | NULL | 4 | +----------+-----------+-------+ Example 2: SELECT * FROM get_tree_relation(2) ORDER BY level ASC; +----------+-----------+-------+ | child_id | parent_id | level | +----------+-----------+-------+ | 2 | NULL | 1 | +----------+-----------+-------+ Example 2: SELECT * FROM get_tree_relation(11) ORDER BY level ASC; +----------+-----------+-------+ | child_id | parent_id | level | +----------+-----------+-------+ +----------+-----------+-------+ I have a PL/PGSQL function that does this for me with some nested selects inside a loop, but my NEW problem is that I need to be able to detect circular loops. For example, if child_id refers to itself or if a parent_id refers to a child_id that is already in the heirarchy we don't want to get into an infinite loop. So I modified my function to use a TEMP table to store the records I had already seen, but then I had problems with the temp table: http://archives.postgresql.org/pgsql-bugs/2003-05/msg00084.php Without having to recompile any database code, can this process be build using out-of-the-box PostgreSQL features? There's gotta be an easy way to do this. It's a fairly common problem, isn't it? --Dante
pgsql-general by date: