pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrows in order
5+ messages / 5 participants
[nested] [flat]
* rows in order
@ 2002-09-30 18:29 Camila Rocha <camilarrocha@hotmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Camila Rocha @ 2002-09-30 18:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: rows in order
@ 2002-10-04 13:36 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Camila Rocha <camilarrocha@hotmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Achilleus Mantzios @ 2002-10-04 13:36 UTC (permalink / raw)
To: Camila Rocha <camilarrocha@hotmail.com>; +Cc: pgsql-sql
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
==================================================================
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: rows in order
@ 2002-10-04 13:37 Richard Huxton <dev@archonet.com>
parent: Camila Rocha <camilarrocha@hotmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2002-10-04 13:37 UTC (permalink / raw)
To: Camila Rocha <camilarrocha@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: rows in order
@ 2002-10-04 15:05 Oleg Bartunov <oleg@sai.msu.su>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Oleg Bartunov @ 2002-10-04 15:05 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Camila Rocha <camilarrocha@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: rows in order
@ 2002-10-04 15:51 Joe Conway <mail@joeconway.com>
parent: Camila Rocha <camilarrocha@hotmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2002-10-04 15:51 UTC (permalink / raw)
To: Camila Rocha <camilarrocha@hotmail.com>; +Cc: pgsql-sql
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~row4
row6 | 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-10-04 15:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-30 18:29 rows in order Camila Rocha <camilarrocha@hotmail.com>
2002-10-04 13:36 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-10-04 15:05 ` Oleg Bartunov <oleg@sai.msu.su>
2002-10-04 13:37 ` Richard Huxton <dev@archonet.com>
2002-10-04 15:51 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox