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