From: Frank Joerdens <frank@joerdens.de>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Tree structure table normalization problem (do I need a trigger?)
Date: Tue, 19 Dec 2000 19:43:57 +0100
Message-ID: <3A3FAC6D.AF5BB5FF@joerdens.de> (raw)
References: <web-1167250@davinci.ethosmedia.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: frank@joerdens.de, josh@agliodbs.com
Subject: Re: Tree structure table normalization problem (do I need a trigger?)
In-Reply-To: <3A3FAC6D.AF5BB5FF@joerdens.de>
References: <web-1167250@davinci.ethosmedia.com> <3A3FAC6D.AF5BB5FF@joerdens.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox