Message-ID: <3DF7777A.41DDD72B@ucia.gov>
Date: Wed, 11 Dec 2002 12:35:54 -0500
From: David M <davidgm0@ucia.gov>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: trigger to maintain relationships
References: <3DF770E3.1C4FABFD@ucia.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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