Re: Tree structure table normalization problem (do I need a trigger?)

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