PostgreSQL创建触发器函数时出现语法错误

问题重现 #

原创建触发器函数的代码：

CREATE OR REPLACE FUNCTION pdate_day_month_decade_trigger()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW."dayMonth" <> OLD."dayMonth" THEN
        DECLARE decadeMonthValue INTEGER := (
            CASE
              WHEN NEW."dayMonth" >= 1 AND NEW."dayMonth" <= 10 THEN 1
              WHEN NEW."dayMonth" >= 11 AND NEW."dayMonth" <= 20 THEN 2
              ELSE 3
            END
         );
         UPDATE main."YDate"
         SET "decadeMonth"=decadeMonthValue
         WHERE id = NEW.id;
    END IF;

    RETURN NEW;
END;
$$

执行后报错：

ERROR:  syntax error (at or near: "SET")
LINE 16:    SET "decadeMonth"=decadeMonthValue
            ^
CONTEXT:  wrong type name "main."YDate"
         SET "decadeMonth"" 

ОШИБКА:  syntax error (at or near: "SET")
SQL state: 42601
Character: 394

错误原因 #

在PL/pgSQL中，当在IF块内部使用DECLARE声明变量时，声明部分之后的执行语句必须包裹在BEGIN...END块中，否则PostgreSQL会将后续SQL语句误认为是声明的一部分，触发语法错误。

修复后的代码 #

CREATE OR REPLACE FUNCTION pdate_day_month_decade_trigger()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW."dayMonth" <> OLD."dayMonth" THEN
        DECLARE decadeMonthValue INTEGER := (
            CASE
              WHEN NEW."dayMonth" >= 1 AND NEW."dayMonth" <= 10 THEN 1
              WHEN NEW."dayMonth" >= 11 AND NEW."dayMonth" <= 20 THEN 2
              ELSE 3
            END
         );
        BEGIN -- 添加BEGIN包裹执行逻辑
            UPDATE main."YDate"
            SET "decadeMonth" = decadeMonthValue
            WHERE id = NEW.id;
        END; -- 添加END闭合块
    END IF;

    RETURN NEW;
END;
$$

额外优化建议 #

• 可直接在SET子句中计算decadeMonth的值，省去变量声明简化代码：

CREATE OR REPLACE FUNCTION pdate_day_month_decade_trigger()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW."dayMonth" <> OLD."dayMonth" THEN
        UPDATE main."YDate"
        SET "decadeMonth" = CASE
                              WHEN NEW."dayMonth" BETWEEN 1 AND 10 THEN 1
                              WHEN NEW."dayMonth" BETWEEN 11 AND 20 THEN 2
                              ELSE 3
                            END
        WHERE id = NEW.id;
    END IF;

    RETURN NEW;
END;
$$

• 注意原函数名pdate_day_month_decade_trigger疑似update的笔误，可根据实际业务需求修正。

内容的提问来源于stack exchange，提问作者Антон Васюхин