Postgres Pro
Downloads
Home   >   mailing lists

Thread: rows in order

rows in order

From
"Camila Rocha"
Date:
Hi!
 
Is there a way to put in order the rows in a table? the problem is that i want to keep a "tree" in the db, but the leaves must be ordered...
does someone have an idea?
 
thanks,
 
Camila

Re: rows in order

From
Achilleus Mantzios
Date:
How do you plan to keep your tree represented??

There are quite a few options for this.
Extensive talk has been made in this list.
Also search the archives.
Basically you can follow
- nested trees (pure sql) aproach
- Genealogical tree representation approach(either using text to represent the path to the parent (pure sql),
orarrays)
- The contrib/tree implementation

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

Re: rows in order

From
Richard Huxton
Date:
On Monday 30 Sep 2002 7:29 pm, Camila Rocha wrote:
> Hi!
>
> Is there a way to put in order the rows in a table? the problem is that i
> want to keep a "tree" in the db, but the leaves must be ordered... does
> someone have an idea?

Do a search on "Joe Celko" and "tree" and you should find a clean way to model
trees in SQL.

--  Richard Huxton

Re: rows in order

From
Oleg Bartunov
Date:
On Fri, 4 Oct 2002, Achilleus Mantzios wrote:

>
> How do you plan to keep your tree represented??
>
> There are quite a few options for this.
> Extensive talk has been made in this list.
> Also search the archives.
> Basically you can follow
> - nested trees (pure sql) aproach
> - Genealogical tree representation approach
>  (either using text to represent the path to the parent (pure sql), or
>  arrays)
> - The contrib/tree implementation

contrib/ltree !

>
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel:    +30-10-8981112
> fax:    +30-10-8981877
> email:  achill@matrix.gatewaynet.com
>         mantzios@softlab.ece.ntua.gr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Re: rows in order

From
Joe Conway
Date:
Camila Rocha wrote:
> Is there a way to put in order the rows in a table? the problem is that i w=
> ant to keep a "tree" in the db, but the leaves must be ordered...
> does someone have an idea?

If you don't mind trying 7.3 beta, there is a function called connectby() in 
contrib/tablefunc. It works like this:

CREATE TABLE connectby_tree(keyid text, parent_keyid text);
INSERT INTO connectby_tree VALUES('row1',NULL);
INSERT INTO connectby_tree VALUES('row2','row1');
INSERT INTO connectby_tree VALUES('row3','row1');
INSERT INTO connectby_tree VALUES('row4','row2');
INSERT INTO connectby_tree VALUES('row5','row2');
INSERT INTO connectby_tree VALUES('row6','row4');
INSERT INTO connectby_tree VALUES('row7','row3');
INSERT INTO connectby_tree VALUES('row8','row6');
INSERT INTO connectby_tree VALUES('row9','row5');

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+--------------------- row2  |              |     0 | row2 row4  | row2         |     1 |
row2~row4row6  | row4         |     2 | row2~row4~row6 row8  | row6         |     3 | row2~row4~row6~row8 row5  | row2
      |     1 | row2~row5 row9  | row5         |     2 | row2~row5~row9
 
(6 rows)

This allows completely dynamically generated trees.

There is also a contrib/ltree, which I believe creates a persistent structure 
for the tree information, and gives you tools to manipulate it (but I have 
never used it, so my discription may not be completely accurate).

HTH,

Joe