From: David M <davidgm0@ucia.gov>
To: pgsql-sql@postgresql.org
Subject: trigger to maintain relationships
Date: Wed, 11 Dec 2002 12:07:47 -0500
Message-ID: <3DF770E3.1C4FABFD@ucia.gov> (raw)
I am maintaining a set of hierarchical data that looks a lot like a
tree. (And my SQL is very rusty. And I'm new to postgres.)
Questions:
-------------
1.) Is the following a reasonable solution? Is there a
postgres-specific way to handle this better? Is there a good generic
SQL way to handle this?
2.) Can I write pure "SQL" triggers to handle this? Am I getting close
in my first cut (below)?
3.) Any other ideas/suggestions?
I have one table with essentially the nodes of a tree:
nodes
------
node_id integer
parent_id integer references nodes(node_id)
...and other descriptive columns...
I want an easy way to find all the elements of a subtree. Not being
able to think of a good declarative solution, I was thinking about
cheating and maintaining an ancestors table:
ancestors
-----------
node_id integer
ancestor_id integer references nodes(node_id)
I figured I could populate the ancestors table via trigger(s) on the
nodes table. Then I should be able to find a whole subtree of node X
with something like:
select *
from nodes
where node_id in (
select node_id
from ancestors
where ancestor_id = X)
Here's my best guess so far at the triggers (but, obviously, no luck so
far):
--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();
--delete trigger
create function pr_tr_d_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;'
language sql;
create trigger tr_d_nodes after insert
on nodes for each row
execute procedure pr_tr_d_nodes();
--update trigger
create function pr_tr_u_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_u_nodes after insert
on nodes for each row
execute procedure pr_tr_u_nodes();
I realize the update trigger could be handled a multitude of ways and
that my first guess may be pretty lousy. But I figured the
insert/update triggers would be pretty straightforward. Am I missing
something basic? I also tried things like (following the one example in
the reference manual):
--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;
return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: davidgm0@ucia.gov
Subject: Re: trigger to maintain relationships
In-Reply-To: <3DF770E3.1C4FABFD@ucia.gov>
* 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