使用 IF ELSE 时 Postgresql 触发器失败

Question

我有这段代码(实际上有效):

    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;

当我改用 IF ELSE 时实际上失败了:

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;

错误如下:

ERROR: syntax error at end of input

LINE 21: $checkRank$ LANGUAGE plpgsql;

任何人都知道出了什么问题，我无法通过 Google 解决...

Answer 1

您需要使用 ELSIF 而不是 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