X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id AEB3B9DCC4D for ; Tue, 14 Mar 2006 08:25:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19112-08 for ; Tue, 14 Mar 2006 08:25:26 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from matrix.gatewaynet.com (host2.dynacom.ondsl.gr [62.103.35.210]) by postgresql.org (Postfix) with ESMTP id 08DAC9DCC48 for ; Tue, 14 Mar 2006 08:25:20 -0400 (AST) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id k2EDUEOp017009; Tue, 14 Mar 2006 15:30:14 +0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id k2EDUBq4017005; Tue, 14 Mar 2006 15:30:11 +0200 Date: Tue, 14 Mar 2006 15:30:11 +0200 (EET) From: Achilleus Mantzios To: Jean-Paul Argudo cc: Daniel Caune , Michael Fuhr , , postgresql sql list Subject: Re: connectby documentation In-Reply-To: <4416ACE1.5040207@argudo.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-7 Content-Transfer-Encoding: 8BIT X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.111 required=5 tests=[AWL=0.111] X-Spam-Score: 0.111 X-Spam-Level: X-Archive-Number: 200603/219 X-Sequence-Number: 24565 O Jean-Paul Argudo έγραψε στις Mar 14, 2006 : > Daniel Caune a ιcrit : > > Wow, that was the quest for the Holy Grail! :-) > > Yes I understand. That kind of documentation for a > contrib-addon-whatever for PostgreSQL can be tricky sometimes to find.. > > > I just jump on that thread to place a reminder for all those wanting to > implement trees in databases, just in case they are still thinking about > howto do that. > > I wroted an article on that topic (in french only sorry : > http://www.postgresqlfr.org/?q=node/142), where I compare id/parent_id, > nested loops and Miguel Sofer's method. > > This method is explained on OpenACS forums (in english) > > http://openacs.org/forums/message-view?message_id=18365 > > The original work of Miguel Sofer (with a PostgreSQL implementation as > an example) can be found here: > > http://www.utdt.edu/~mig/sql-trees/ > > Be sure to download the tar.gz. on the like "here"... and read his draft. > > I'm really convinced this method is the best so far. I used it in 3 > different projects where I had to implement big trees structures on a > table. They all still work with no problem of any kind. I agree, this genealogical approach is i think the most intuitive/efficient, however this depends on the nature of the intented operation types. One implementation of this (i think) is the ltree contrib module. Haven't worked with this tho. What i actually did for my ultra demanding task (modeling inventory maintenance of 709772 machinery items/parts etc... of ~ 40 vessels), was smth of the type defid | integer | not null default nextval('public.machdefs_defid_seq'::text) parents | integer[] | description | text | machtypeid | integer .......... where parents hold the path from the item's direct parent to its root ancestor, and tree queries are done with a help of a intarray index on parents "machdefs_parents" gist (parents gist__intbig_ops) > > Just to let you know in case you missed that ;-) > > My 2 ’ > > -- > Jean-Paul Argudo > www.Argudo.org > www.PostgreSQLFr.org > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- -Achilleus