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