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