Обсуждение: Pl/pgSQL trigger failing and i ant see why

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.

immediateparentid_  is INT4

This fails NEW.immediateparentid_ := FindRealParent(NEW.messageid_) ;
This works NEW.immediateparentid_ := 123456 ;

Here are the function's btw I have tested the interactivlky so they
are returning coorect values.

Any one got any ideas? BTW WTF does postgres hide its logs?

Best Regards

Maurice

CREATE FUNCTION FindRealParent(INTEGER) returns INTEGER as '
  DECLARE
     realid INTEGER;
  BEGIN
     realid := findbyref($1);
     IF realid = 0 THEN
        realid := findbysubject($1);
     END IF;
     RETURN realid;
  END;
' LANGUAGE  'plpgsql';



CREATE TRIGGER SetMsgParent BEFORE INSERT  on messages_
FOR EACH ROW EXECUTE PROCEDURE SetMsgParent();



CREATE FUNCTION SetMsgParent () returns OPAQUE as '
BEGIN
   NEW.immediateparentid_ := FindRealParent(NEW.messageid_) ;
   RETURN NEW;
END;
' LANGUAGE  'plpgsql';




CREATE FUNCTION FindRealParent(INTEGER) returns INTEGER as '
  DECLARE
     realid INTEGER;
  BEGIN
     realid := findbyref($1);
     IF realid = 0 THEN
        realid := findbysubject($1);
     END IF;
     RETURN realid;
  END;
' LANGUAGE  'plpgsql';

On 24 Jul 2002, Maurice Walshe wrote:

> 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.
>
> immediateparentid_  is INT4
>
> This fails NEW.immediateparentid_ := FindRealParent(NEW.messageid_) ;
> This works NEW.immediateparentid_ := 123456 ;
>
> Here are the function's btw I have tested the interactivlky so they
> are returning coorect values.
>

What are you getting as an error when you insert to the table?

> Any one got any ideas? BTW WTF does postgres hide its logs?

General logs?  That depends on your configuration.  You either should
be redirecting output/error from postmaster to somewhere or having
it go to syslog.

On Thursday 25 Jul 2002 3:58 pm, Maurice Walshe wrote:
> Hi Richard
>
> I have narowed it down a bit it now seems that
> NEW only works from some types of variables

Hmm - haven't seen this before.

> when I try and get the message subject and headers.
>
> like this....

>    RAISE NOTICE ''memebrid(%)'', NEW.memberid_;
>    RAISE NOTICE ''hdrall(%)'', NEW.hdrall_;
>    RAISE NOTICE ''calling setmsgparent(%)'', NEW.hdrsubject_;

> 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

Try the following - cut and paste this into a file, save it then use
  \i <filename>
in psql to see what it does.

== BEGIN file ==
DROP SEQUENCE trigtest_seq;
CREATE SEQUENCE trigtest_seq;

DROP TABLE trigtest;
CREATE TABLE trigtest (
    test_id    int NOT NULL DEFAULT nextval('trigtest_seq'),
    test_subj  varchar(200),
    test_all   text
);

DROP FUNCTION tt_trig();
CREATE FUNCTION tt_trig() RETURNS OPAQUE AS '
DECLARE
   mymsgid INTEGER;
   myHeader TEXT;
   mySubject TEXT;
   newparent INTEGER;
BEGIN
   mymsgid := NEW.test_id;
   myHeader := NEW.test_all;
   mySubject := NEW.test_subj;
   RAISE NOTICE ''Vars = % / % / %'', mymsgid, myHeader,
      mySubject;
   NEW.test_subj := ''xxx'' || NEW.test_subj;
   RETURN NEW ;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER tt_test_trigger BEFORE INSERT  on trigtest
    FOR EACH ROW EXECUTE PROCEDURE tt_trig();

INSERT INTO trigtest (test_subj,test_all) VALUES ('one','one all');
INSERT INTO trigtest (test_subj,test_all) VALUES ('two','two all');
INSERT INTO trigtest (test_subj) VALUES ('three');
SELECT * FROM trigtest ORDER BY test_id;

== End file ==

I get the following:
richardh=> \i test_pg_trig.txt
DROP
CREATE
DROP
CREATE
DROP
CREATE
CREATE
psql:test_pg_trig.txt:32: NOTICE:  trigtest_seq.nextval: sequence was
re-created
psql:test_pg_trig.txt:32: NOTICE:  Vars = 1 / one all / one
INSERT 7023561 1
psql:test_pg_trig.txt:33: NOTICE:  Vars = 2 / two all / two
INSERT 7023562 1
psql:test_pg_trig.txt:34: NOTICE:  Vars = 3 / <NULL> / three
INSERT 7023563 1
 test_id | test_subj | test_all
---------+-----------+----------
       1 | xxxone    | one all
       2 | xxxtwo    | two all
       3 | xxxthree  |
(3 rows)

- Richard Huxton

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