Received: from grover.localnet (thesof1.lnk.telstra.net [139.130.119.126]) by hub.org (8.10.1/8.10.1) with ESMTP id e6IN9vh54656 for ; Tue, 18 Jul 2000 19:09:58 -0400 (EDT) Received: from TheSoftwareSmith.Com.Au (IDENT:andrew@grover [192.168.1.3]) by grover.localnet (8.9.3/8.9.3) with ESMTP id KAA29933; Wed, 19 Jul 2000 10:19:03 +1000 Message-ID: <3974F3F7.1AABD0C7@TheSoftwareSmith.Com.Au> Date: Wed, 19 Jul 2000 10:19:03 +1000 From: andrew Reply-To: TheDog@TheSoftwareSmith.Com.Au Organization: The Software Smith X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: drfrog@smartt.com, pgsql-sql@postgresql.org Subject: Re: using a self referencing table References: <3974805D.6C33895E@smartt.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200007/179 drfrog@smartt.com wrote: > i have a table with this structure > > name (varchar)|category id (int4)|parent category id (int4)|leaf node > (bool) > > im trying to make a perl script that should tree the info > > parent cat > subcat > subcat2 > subcat2 > subcat > subcat2 > ... > > but im having troubles wrapping my head around this > > im using the Pg modules to access postgres > > if anyone has any suggestions please lemme know thanks! The trick is to be able to sort all the elements of the hierarchy so that they come out in the right order, that is, grouped by parent, and then indent them. You can do this with two extra redundant fields, clevel int and csort text. Every time you insert a node in the tree, include the calculated level of the node (i.e. clevel = parent->clevel + 1) and a string which ensures that the children of a given node are grouped together (i.e. csort = parent->csort + category id::text). Probably best to left pad the category id with an appropriate number of zeroes for this calculation. When you retrieve the nodes, order by csort, and convert clevel into the appropriate number of indents.