Postgresql trigger fails when using IF ELSE

Question

I have this piece of code (which actually works):

    CREATE or replace FUNCTION checkRank() RETURNS trigger AS $checkRank$

    DECLARE
    old_rank varChar;
    old_date date;

    BEGIN 

--  SELECT a.rank INTO old_rank from RANK a where a.account = new.account;
    SELECT a.rank, a.date INTO old_rank, old_date from RANK a where a.account = new.account order by date desc LIMIT 1;

        -- Check if inserted rank != current rank           
        if old_rank = new.rank THEN
            RAISE EXCEPTION 'rank unchanged';
        END IF;
                if new.date < old_date THEN
            RAISE EXCEPTION 'old rank';
        END IF;

        RETURN NEW;
    END
$checkRank$ LANGUAGE plpgsql;

which actually fails when I use an IF ELSE instead:

CREATE or replace FUNCTION checkRank() RETURNS trigger AS $checkRank$

    DECLARE
    old_rank varChar;
    old_date date;

    BEGIN 

--  SELECT a.rank INTO old_rank from RANK a where a.account = new.account;
    SELECT a.rank, a.date INTO old_rank, old_date from RANK a where a.account = new.account order by date desc LIMIT 1;

        -- Check if inserted rank != current rank           
        IF old_rank = new.rank THEN
            RAISE EXCEPTION 'rank unchanged';
        ELSE IF new.date < old_date THEN
            RAISE EXCEPTION 'old rank';
        ELSE
        RETURN NEW;
        END IF;
    END
$checkRank$ LANGUAGE plpgsql;

with the following error:

ERROR: syntax error at end of input

LINE 21: $checkRank$ LANGUAGE plpgsql;

Anyone has a clue what's going wrong, I can't figure it out with Google...

Lukasz Szozda · Accepted Answer · 2018-10-14 14:06:11Z

1

You need to use ELSIF instead of ELSE IF:

IF old_rank = new.rank THEN
    RAISE EXCEPTION 'rank unchanged';
ELSIF new.date < old_date THEN
    RAISE EXCEPTION 'old rank';
ELSE
    RETURN NEW;
END IF;

db<>fiddle demo

41.6.2. Conditionals

PL/pgSQL has three forms of IF:

IF ... THEN ... END IF

IF ... THEN ... ELSE ... END IF

IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

answered Oct 14, 2018 at 14:06

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

Postgresql trigger fails when using IF ELSE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related