Thread: 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 > 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) > >
Quoth tmp <skrald@amossen.dk>: > Are there any plans on implementing support for recursive queries in > postgresql in the near future? If so: When? > > I can see there has been some discussion on the subject in the > developer-group for quite some time ago, but aparently all thoughts > of recursive queries has been stalled. :-( Other things have headed higher in terms of urgency. If someone volunteers to work on it, or if someone sponsors someone to work on it, that would doubtless increase its priority for 8.1 or such. I'd love to see it; the syntax provides ways to make complex queries a lot cleaner even if you're not recursing. -- select 'cbbrowne' || '@' || 'gmail.com'; http://www.ntlug.org/~cbbrowne/rdbms.html Be careful when a loop exits to the same place from side and bottom.
On Mon, Jan 24, 2005 at 06:30:52PM -0500, Christopher Browne wrote: > Quoth tmp <skrald@amossen.dk>: > > Are there any plans on implementing support for recursive queries in > > postgresql in the near future? If so: When? > > > > I can see there has been some discussion on the subject in the > > developer-group for quite some time ago, but aparently all thoughts > > of recursive queries has been stalled. :-( > > Other things have headed higher in terms of urgency. > > If someone volunteers to work on it, or if someone sponsors someone to > work on it, that would doubtless increase its priority for 8.1 or > such. > > I'd love to see it; the syntax provides ways to make complex queries a > lot cleaner even if you're not recursing. Depending on what you're doing you could also use contrib/ltree instead of a recursive query. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"