column "new" of relation "publications" does not exist

Question

I want to develop a trigger that updates a trigger after an insert or update.

I follow this tutorial and created a function that is called by the trigger after an insertion. I tried with both after and before.

DROP TRIGGER IF EXISTS answer_update_question_timestamp ON public.publications;

CREATE OR REPLACE FUNCTION trigger_update_question_timestamp()
  RETURNS TRIGGER AS $func$
BEGIN
    UPDATE publications SET new.last_edit_date = now() WHERE publicationid = new.publicationid;
    RETURN NULL;
END;
$func$  LANGUAGE plpgsql;

CREATE TRIGGER answer_update_question_timestamp AFTER INSERT OR UPDATE ON publications
   FOR EACH ROW EXECUTE PROCEDURE trigger_update_question_timestamp();




CREATE TABLE publications
(
    publicationid SERIAL PRIMARY KEY,
    body VARCHAR(1000) NOT NULL ,
    creation_date TIMESTAMP DEFAULT now() NOT NULL,
    userid INTEGER NOT NULL,
    last_edit_date TIMESTAMP,
    CONSTRAINT body_length CHECK (CHAR_LENGTH(body) >= 10 AND CHAR_LENGTH(body) <= 1000),
    CONSTRAINT "FK_publications_users"
        FOREIGN KEY ("userid") REFERENCES users ("userid") ON DELETE SET NULL ON UPDATE CASCADE
);

I noticed that NEW doesn't work with operations such as FOR EACH STATEMENT but I haven't found anything wrong with FOR EACH ROW

Does anyone kno what I'm doing wrong?

Kind regards

Answer 1

If you want last_edit_date to be set to now() on insert or delete, just set default to now():

CREATE TABLE publications
(
 ...
    last_edit_date TIMESTAMP DEFAULT now(),
....
);

If you still want to go there,instead of updating itself and returning null assign a new value to NEW:

NEW.last_edit_date := now(); 
RETURN NEW;

Endless loop pozs is talking about happens when you assign a function that updates a table to a trigger on that table that fires on update

Update - working example for OP:

t=# CREATE TABLE publications
(
    publicationid SERIAL PRIMARY KEY,
    body VARCHAR(1000) NOT NULL ,
    creation_date TIMESTAMP DEFAULT now() NOT NULL,
    userid INTEGER NOT NULL,
    last_edit_date TIMESTAMP
);
CREATE TABLE
t=# CREATE OR REPLACE FUNCTION trigger_update_question_timestamp()
  RETURNS TRIGGER AS $func$
BEGIN
    new.last_edit_date := now();
    RETURN NEW;
END;
$func$  LANGUAGE plpgsql;
CREATE FUNCTION
t=# CREATE TRIGGER answer_update_question_timestamp BEFORE INSERT OR UPDATE ON publications
   FOR EACH ROW EXECUTE PROCEDURE trigger_update_question_timestamp();
CREATE TRIGGER
t=# insert into publications select 1,'2',now(),3,null;
INSERT 0 1
t=# select * from publications;
 publicationid | body |       creation_date        | userid |       last_edit_date
---------------+------+----------------------------+--------+----------------------------
             1 | 2    | 2017-03-30 15:28:10.897846 |      3 | 2017-03-30 15:28:10.897846
(1 row)