Re: trigger to maintain relationships

David,

> 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();

Ummm ... no.

Within the trigger produre, NEW is a record variable, and its fields
are values.  You cannot SELECT from NEW.  You're also missing the parts
of a PLPGSQL procedure.  What you want is:

create function pr_tr_i_nodes() returns opaque
> as '
DECLARE v_ancestor INT;
BEGIN
SELECT ancestor_id INTO v_ancestor
FROM ancestors WHERE ancestors.node_id = NEW.parent_id;
INSERT INTO ancestors
VALUES ( NEW.node_id, v_ancestor );
>     return NEW;
END;'
> language 'plpgsql';

-Josh Berkus