I think I figured out my join syntax error (sorry for confusing the issue
with noise like that). I'd still be interested in general comments on
design.
FYI, join should've looked like:
create function pr_tr_i_nodes() returns opaque
as ' insert into ancestors select NEW.node_id, ancestor_id from NEW left outer join ancestors on (NEW.parent_id
=
ancestors.node_id);
return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert on nodes for each row execute procedure pr_tr_i_nodes();
David M wrote:
> 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 majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
