Recursive queries
I have a database with the following fields:
product_id INTEGER
parent_id INTEGER
name VARCHAR(64)
The idea is that it will store a tree of products and its subproducts. Any
row whose parent_id is NULL will be assumed to be a root product (i.e. top
level).
This means I will need to process recursively, with some sort of reference to
parent_id passing through layers of recursion.
However, I want to save on database queries and do a "SELECT *", then fetch
each row on the results recursively, to build the tree. The idea is to only
have to do one db query.
Does anyone have any idea how this can be done in PostgreSQL? Both Oracle and
DB2 support this with their own proprietary syntax.
--
Juan Jose Comellas
(juanjo@comellas.org)
There was a detailed post by Joe Celko not long ago, which discussed how to
handle trees in SQL. I suggest you look at the list archives to find this.
----- Original Message -----
From: "Juan Jose Comellas" <juanjo@comellas.org>
To: <pgsql-general@postgresql.org>
Sent: Friday, September 21, 2001 11:14 AM
Subject: [GENERAL] Recursive queries
I have a database with the following fields:
product_id INTEGER
parent_id INTEGER
name VARCHAR(64)The idea is that it will store a tree of products and its subproducts. Any
row whose parent_id is NULL will be assumed to be a root product (i.e. top
level).This means I will need to process recursively, with some sort of reference
to
parent_id passing through layers of recursion.
However, I want to save on database queries and do a "SELECT *", then
fetch
each row on the results recursively, to build the tree. The idea is to
only
have to do one db query.
Does anyone have any idea how this can be done in PostgreSQL? Both Oracle
and
Show quoted text
DB2 support this with their own proprietary syntax.
--
Juan Jose Comellas
(juanjo@comellas.org)---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)