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();
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
