Syntax error when calling function inside if statement in trigger function

Question

I'm trying to create a trigger function that simply calls a function within an if statement:

CREATE OR REPLACE FUNCTION public."onTrack"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        "updateUserStats"(NEW."userId");
    ELSIF (TG_OP = 'DELETE') THEN
        "updateUserStats"(OLD."userId");
    END IF

    RETURN NULL
END
$BODY$;

However, when I try to create the function, I get this error

I'm not sure what I'm doing wrong, as according to the docs, this syntax is correct. Removing the quotes around the functions will not work as the name is case sensitive and also is still a syntax error.

I am on version "PostgreSQL 9.5.14"

Oh, that seemed to work. Make it an answer? Can't believe I missed this when reading the docs. — Sebastian Olsen
– Sebastian Olsen, Commented Sep 11, 2018 at 8:48
Unrelated, but: you should really avoid quoted identifiers ("onTrack", "userId", ...) they are much more trouble than they are worth it. — user330315
– user330315, Commented Sep 11, 2018 at 9:26
They are named that way for consistency. I've not really had any trouble with them at all, it's down to preference imo. — Sebastian Olsen
– Sebastian Olsen, Commented Sep 11, 2018 at 9:34

Mureinik · Accepted Answer · 2018-09-11 08:53:52Z

1

You seem to be missing the perform keyword:

IF (TG_OP = 'INSERT') THEN
    PERFORM "updateUserStats"(NEW."userId");
ELSIF (TG_OP = 'DELETE') THEN
    PERFORM "updateUserStats"(OLD."userId");
END IF;

answered Sep 11, 2018 at 8:53

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oto Shavadze · Accepted Answer · 2018-09-11 08:52:47Z

1

You just need to call that function:

...
perform updateUserStats(NEW.userId);
...

also you missed semicolons after END IF and RETURN NULL

answered Sep 11, 2018 at 8:52

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

Collectives™ on Stack Overflow

Syntax error when calling function inside if statement in trigger function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related