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