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)