PostgreSQL函数IF语句报语法错误，原因是什么？

编写PostgreSQL函数时遇到语法错误，需求为：若FoodType表中存在指定FoodTypeName的行，返回该行并标记existing；若不存在则插入新行，返回该行并标记new。

报错信息 #

ERROR:  syntax error at or near "IF"
LINE 11:   IF EXISTS (
           ^
SQL state: 42601
Character: 208

原函数代码 #

CREATE FUNCTION create_food_type (
  foodTypeName TEXT,
  foodTypeIsActive BOOLEAN,
  foodTypeCreatedBy INT,
  foodTypeModifiedBy INT
)
RETURNS TABLE (operation TEXT, result JSON)
LANGUAGE SQL
AS $$
BEGIN
  IF EXISTS (
    SELECT *
    FROM "FoodType"
    WHERE "FoodTypeName" = foodTypeName
  ) THEN
    -- Return the existing row and flag indicating row already exists
    RETURN QUERY (
      SELECT 'existing' AS operation, json_build_object(
        'FoodTypeId', "FoodTypeID",
        'FoodTypeName', "FoodTypeName",
        'FoodTypeIsActive', "FoodTypeIsActive",
        'FoodTypeCreatedDate', "FoodTypeCreatedDate",
        'FoodTypeCreatedBy', "FoodTypeCreatedBy",
        'FoodTypeModifiedDate', "FoodTypeModifiedDate",
        'FoodTypeModifiedBy', "FoodTypeModifiedBy"
      )
      FROM "FoodType"
      WHERE "FoodTypeName" = foodTypeName
      LIMIT 1
    );
  ELSE
    -- Insert a new row and return the newly inserted row and flag indicating new row added
    RETURN QUERY (
      INSERT INTO "FoodType" ("FoodTypeName", "FoodTypeIsActive", "FoodTypeCreatedDate", "FoodTypeCreatedBy", "FoodTypeModifiedDate", "FoodTypeModifiedBy")
      VALUES (foodTypeName, foodTypeIsActive, CURRENT_TIMESTAMP, foodTypeCreatedBy, CURRENT_TIMESTAMP, foodTypeModifiedBy)
      RETURNING 'new' AS operation, json_build_object(
        'FoodTypeId', "FoodTypeID",
        'FoodTypeName', "FoodTypeName",
        'FoodTypeIsActive', "FoodTypeIsActive",
        'FoodTypeCreatedDate', "FoodTypeCreatedDate",
        'FoodTypeCreatedBy', "FoodTypeCreatedBy",
        'FoodTypeModifiedDate', "FoodTypeModifiedDate",
        'FoodTypeModifiedBy', "FoodTypeModifiedBy"
      )
    );
  END IF;
END;
$$;

尝试改用CASE WHEN逻辑，仍在对应位置报语法错误。

问题根源 #

原函数指定了LANGUAGE SQL，但SQL语言的函数不支持IF、BEGIN/END这类流程控制语句——这些是PL/pgSQL语言的专属特性，这就是报错的直接原因。

修正后的代码 #

将函数语言改为plpgsql，同时优化逻辑避免重复查询，确保并发场景下的一致性：

CREATE OR REPLACE FUNCTION create_food_type (
  p_foodTypeName TEXT,
  p_foodTypeIsActive BOOLEAN,
  p_foodTypeCreatedBy INT,
  p_foodTypeModifiedBy INT
)
RETURNS TABLE (operation TEXT, result JSON)
LANGUAGE plpgsql
AS $$
DECLARE
  v_existing_row "FoodType"%ROWTYPE;
BEGIN
  -- 查询现有行并加锁，防止并发插入导致重复数据
  SELECT * INTO v_existing_row
  FROM "FoodType"
  WHERE "FoodTypeName" = p_foodTypeName
  FOR UPDATE;

  IF FOUND THEN
    -- 返回已存在的行
    RETURN QUERY SELECT 'existing' AS operation, json_build_object(
      'FoodTypeId', v_existing_row."FoodTypeID",
      'FoodTypeName', v_existing_row."FoodTypeName",
      'FoodTypeIsActive', v_existing_row."FoodTypeIsActive",
      'FoodTypeCreatedDate', v_existing_row."FoodTypeCreatedDate",
      'FoodTypeCreatedBy', v_existing_row."FoodTypeCreatedBy",
      'FoodTypeModifiedDate', v_existing_row."FoodTypeModifiedDate",
      'FoodTypeModifiedBy', v_existing_row."FoodTypeModifiedBy"
    );
  ELSE
    -- 插入新行并返回结果
    RETURN QUERY INSERT INTO "FoodType" (
      "FoodTypeName", 
      "FoodTypeIsActive", 
      "FoodTypeCreatedDate", 
      "FoodTypeCreatedBy", 
      "FoodTypeModifiedDate", 
      "FoodTypeModifiedBy"
    )
    VALUES (
      p_foodTypeName, 
      p_foodTypeIsActive, 
      CURRENT_TIMESTAMP, 
      p_foodTypeCreatedBy, 
      CURRENT_TIMESTAMP, 
      p_foodTypeModifiedBy
    )
    RETURNING 'new' AS operation, json_build_object(
      'FoodTypeId', "FoodTypeID",
      'FoodTypeName', "FoodTypeName",
      'FoodTypeIsActive', "FoodTypeIsActive",
      'FoodTypeCreatedDate', "FoodTypeCreatedDate",
      'FoodTypeCreatedBy', "FoodTypeCreatedBy",
      'FoodTypeModifiedDate', "FoodTypeModifiedDate",
      'FoodTypeModifiedBy', "FoodTypeModifiedBy"
    );
  END IF;
END;
$$;

关键优化点 #

• 替换LANGUAGE SQL为LANGUAGE plpgsql，启用流程控制支持
• 使用SELECT ... INTO复用查询结果，减少一次表扫描
• 新增FOR UPDATE锁，避免高并发场景下重复插入相同FoodTypeName的行
• 参数前缀加p_、变量前缀加v_，避免与表字段名冲突

内容的提问来源于stack exchange，提问作者Prajwal Abraham