maurice.walshe@poptel.coop (Maurice Walshe) wrote in message news:<534cf093.0207240525.755d88d3@posting.google.com>...
> HI all
> I have read the skimpy docs on postgres triggers and whilst I can
> get a simple trigger working when I try to call a Pl/pgsql function
> to return the value I want to set one of the foields in NEW to it wont
> work.
I have narowed it down a bit it now seems that
NEW only works (postgres 7.1.3) from some types of
variables when I try and get the message subject and headers.
like this....
CREATE FUNCTION SetMsgParent () returns OPAQUE as '
DECLARE
mymsgid INTEGER;
myHeader TEXT;
mySubject TEXT;
newparent INTEGER;
BEGIN
mymsgid := NEW.messageid_;
myHeader := NEW.hdrall_;
mySubject := NEW.hdrsubject_;
RAISE NOTICE ''FIND BEFORE VERSION calling setmsgparent(%)'', mymsgid;
RAISE NOTICE ''memebrid(%)'', NEW.memberid_;
RAISE NOTICE ''hdrall(%)'', NEW.hdrall_;
RAISE NOTICE ''calling setmsgparent(%)'', NEW.hdrsubject_;
NEW.immediateparentid_ := FindRealParent(mymsgid,NEW.hdrall_, NEW.hdrsubject_) ;
RETURN NEW ;
END;
' LANGUAGE 'plpgsql';
NEW.memberid_ is ok and NEW.hdrall_ and NEW.hdrsubject_ are NULL (hdrall_ is
a TEXT hdrsubject_ is varchar(200))
I have tried this as a before and an after trigger