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_relat ion(child_id INTEGER)'
Example 1:
SELECT *
FROM get_tree_relati on(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_relati on(2)
ORDER BY level ASC;
+----------+-----------+-------+
| child_id | parent_id | level |
+----------+-----------+-------+
| 2 | NULL | 1 |
+----------+-----------+-------+
Example 2:
SELECT *
FROM get_tree_relati on(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...5/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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
