syntax Error in PostgreSQL when I try to create Trigger

Question

I want to create trigger in PostgreSQL.

Logic is very simple.

I need trigger, if published_at updated and written_at is null, set published_at to written_at.

I wrote this one, but it failed. Does anybody have an idea?

CREATE function setWrittenAt() RETURNS trigger;
AS 
DECLARE old_id INTEGER;
BEGIN ;
old_id = OLD.id
  IF NEW.published_at IS NOT and NEW.written_at IS null
    THEN
    UPDATE review SET NEW.written_at = NEW.published_at where id = old_id;
 END IF ;
RETURN NEW;
END;
LANGUAGE plpgsql;


CREATE TRIGGER update_written_at 
    AFTER UPDATE OF published_at ON review
    WHEN (OLD.published_at IS DISTINCT FROM NEW.published_at)
    EXECUTE PROCEDURE setWrittenAt();

Error:

Syntax error: 7 ERROR: syntax error at or near "DECLARE" LINE 3: DECLARE old_id INTEGER;

Syntax error: 7 ERROR: syntax error at or near "DECLARE" LINE 3: DECLARE old_id INTEGER; — sdk, May 20 '20 at 07:39
The body of the function (everything from `DECLARE` to `END`) must be a string literal, that is, enclosed with `'` or `$$`. — Laurenz Albe, May 20 '20 at 07:41

score 2 · Answer 1 · 2020-05-20T08:30:22.047

There are multiple errors in your code:

IS NOT is not a valid expression you need IS NOT NULL.
After BEGIN and the returns clause there must be no ;
you forgot to enclose the function body as a string (which is easier to write if you use dollar quoting
you also don't need an unnecessary (additional) UPDATE if you make it a before trigger

CREATE function setwrittenat() 
  RETURNS trigger
AS 
$$
BEGIN
  IF NEW.published_at IS NOT NULL and NEW.written_at IS null THEN
    NEW.written_at := = NEW.published_at; --<< simply assign the value
  END IF;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Then use a BEFORE trigger:

CREATE TRIGGER update_written_at 
    BEFORE UPDATE OF published_at ON review
    WHEN (OLD.published_at IS DISTINCT FROM NEW.published_at)
    FOR EACH ROW
    EXECUTE PROCEDURE setWrittenAt();

score 1 · Accepted Answer · answered May 20 '20 at 08:29

this is based on a_horse_with_no_names answer, since it'll throw an error.

ERROR: statement trigger's WHEN condition cannot reference column values

You need to add FOR EACH ROW, else conditional triggers will not function.

If neither is specified, FOR EACH STATEMENT is the default.

Statement-level triggers can also have WHEN conditions, although the feature is not so useful for them since the condition cannot refer to any values in the table.

See here

CREATE TRIGGER update_written_at
    BEFORE UPDATE OF published_at ON review
    FOR EACH ROW
    WHEN (OLD.published_at IS DISTINCT FROM NEW.published_at)
    EXECUTE PROCEDURE setWrittenAt();

I can not comment yet, which is why I've posted this as an answer.

syntax Error in PostgreSQL when I try to create Trigger

2 Answers2

Linked

Related