Re: Pl/pgSQL trigger failing and i ant see why

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