Received: from rakete.joerdens.de (rakete.joerdens.de [62.104.116.200]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBJIi1605981 for ; Tue, 19 Dec 2000 13:44:01 -0500 (EST) (envelope-from frank@joerdens.de) Received: from limedes.archi-me-des.de (pD900256D.dip.t-dialin.net [217.0.37.109]) by rakete.joerdens.de (8.9.3/8.9.3) with ESMTP id UAA18015; Tue, 19 Dec 2000 20:03:49 +0100 Received: from joerdens.de (tricky.archi-me-des.de [192.168.100.1]) by limedes.archi-me-des.de (8.9.3/8.9.3) with ESMTP id TAA00813; Tue, 19 Dec 2000 19:28:25 +0100 Message-ID: <3A3FAC6D.AF5BB5FF@joerdens.de> Date: Tue, 19 Dec 2000 19:43:57 +0100 From: Frank Joerdens X-Mailer: Mozilla 4.72 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: Josh Berkus CC: pgsql-sql@postgresql.org Subject: Re: Tree structure table normalization problem (do I need a trigger?) References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/220 X-Sequence-Number: 554 Josh Berkus wrote: [ . . . ] > This is exactly why my model includes a "Level" column. I looked at your post from a few days ago again; you did indeed explain about the level column. I missed that somehow and had to reinvent the wheel . . . > > This means > > you need a loop control structure which means you have to > > write a > > PL/pgSQL procedure (or some other procedure) that is run > > by a trigger to > > update the level column on insert or update, as in > > > This seems to feasible but not really as straightforward > > as one might > > hope. Is there an easier way? > > Hmmm. I don't know, Frank. That strikes me as a really > good, straightforward workaround to your problem. I'm not > sure what you could do that would be simpler. This is > practically a textbook example of why triggers are necessary > to retain relational integrity. Cool. And I didn't consult a textbook ;). Actually, it's even simpler than I described above: The function that you run when the trigger fires is plain vanilla sql with a littel subselect thrown in: create function update_level(int4) returns int4 as 'update index set level=(A.level+1) from index as A where A.id = (select parentid from index where id = $1 ) and index.id = $1; select 1 as ignore_this;' LANGUAGE 'sql'; . . . i.e. you just get the level from the higher-up node's level plus 1, rather than walking to the top of the tree and counting the steps. This _doesn't_ work though if you move an entire subtree within the hierarchy to another level. Then you'd need to have a function that walks through the entire subtree to update the level column for every single node . . . hmmm. I'll think about it. I don't think I'll need it for the current project since I'll only allow the moving around of end nodes. Cheers, Frank