473,320 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to navigate tree without CONNECT BY?

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...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

Nov 12 '05 #1
5 15041
D. Dante Lorenso wrote:
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?


See connectby() in contrib/tablefunc.

Joe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

See http://gppl.terminal.ru/readme.html

On Thu, 18 Dec 2003, D. Dante Lorenso wrote:
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...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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3
CoL
Hi

D. Dante Lorenso wrote:
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?


use connect_by from contrib.

C.
Nov 12 '05 #4
Joe Conway wrote:
D. Dante Lorenso wrote:
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?

See connectby() in contrib/tablefunc.

Yep. That's what I was looking for. Had to upgrade to 7.4 and
then install the contrib stuff and import those functions into my
database.

But, what a pain in the butt. I'd think this functionality
would be so important that it'd make it into the main source.
Kinda like MD5 did.

Thanks again.

Dante

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #5
Does anyone now how the algorithm for connect by works? Is it very
efficient for large data sets?

rg

----- Original Message -----
From: "CoL" <co*@mportal.hu>
To: <pg***********@postgresql.org>
Sent: Friday, December 19, 2003 3:17 AM
Subject: Re: [GENERAL] How to navigate tree without CONNECT BY?

Hi

D. Dante Lorenso wrote:
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?


use connect_by from contrib.

C.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(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

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: t_pet422 | last post by:
Hi, I've been scouring the net and reading the PostgreSQL docs for a while now trying to learn how to create a recursive function in PL/pgSQL that will return a whole subtree given a starting...
0
by: travisGatesMcGee | last post by:
For days and days, I have been trying to see a URL's parsing in a DOM tree (links collection, images and other DHTML elements) in Visual Basic......... Total Disaster!!!!!!!!!! Way too many...
4
by: Jerry Khoo | last post by:
Thanks for the answer, and by the way, i would like to know that in C++, how u create a tree using a recursive envronment. It seems that most people used struct to create a node than, using...
14
by: Dave | last post by:
Hello all, After perusing the Standard, I believe it is true to say that once you insert an element into a std::list<>, its location in memory never changes. This makes a std::list<> ideal for...
2
by: My Internet | last post by:
Hello, I want to know if in PostgreSQL, there is a command equivalent to START WITH... CONNECT BY from Orcale? This command is used to traverse a tree. Thank you, Jean-Marc ...
19
by: Christian Fowler | last post by:
I have a VERY LARGE pile of geographic data that I am importing into a database (db of choice is postgres, though may hop to oracle if necessary). The data is strictly hierarchical - each node has...
3
by: piotrek | last post by:
Hi I would like to ask you a question. Ian creating app. that download from server directory structure ( whole tree ) and those data are placed in proper places into my treeview control. I...
22
by: delraydog | last post by:
It's quite simple to walk to the DOM tree going forward however I can't figure out a nice clean way to walk the DOM tree in reverse. Checking previousSibling is not sufficient as the...
1
by: Satish.Talyan | last post by:
hi, i want to create a dynamic tree hierarchy in javascript.there are two parts in tree, group & user.when we click on group then users come under that's tree category will be opened.problem is...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.