Message-ID: <3D9DB90E.3010101@joeconway.com>
Date: Fri, 04 Oct 2002 08:51:42 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Camila Rocha <camilarrocha@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: rows in order
References: <OE40vN3qljWwY0VnkB4000054b0@hotmail.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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