Postgresql函数在If处有语法错误。为什么?

huangapple go评论62阅读模式
英文:

Postgresql function has syntax error at If. Why?

问题

I am new to writing functions on postgres. So I was trying to write a function that checks if a row exists. If it exists then it returns the row with a flag 'Exists', else it inserts a row and returns it with a flag 'New'.

但我面临一个我找不到的简单语法错误。

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

这是SQL函数。

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 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
    -- 插入新行并返回新插入的行和指示新行已添加的标志
    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;
$$;

I have tried CASE WHEN as well.

我也尝试过使用CASE WHEN。

 IF EXISTS (
    SELECT *
    FROM "FoodType"
    WHERE "FoodTypeName" = foodTypeName
  ) THEN
--Do Something
 ELSE
--Do Something
 END IF;

Even this throws the same error at 'CASE'.

英文:

I am new to writing functions on postgres. So I was trying to write a function that checks if a row exists. If it exists then it returns the row with a flag 'Exists', else it inserts a row and returns it with a flag 'New'.

But I am facing a simple syntax error which I am not able to find out.
> ERROR: syntax error at or near "IF"
> LINE 11: IF EXISTS (
> ^
> SQL state: 42601
> Character: 208

This is the sql function.

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;
$$;

I have tried CASE WHEN as well.

 IF EXISTS (
    SELECT *
    FROM "FoodType"
    WHERE "FoodTypeName" = foodTypeName
  ) THEN
--Do Something
 ELSE
--Do Something
 END IF;

Even this throws the same error at 'CASE'

答案1

得分: 0

The body language should be plpgsql, also add suffix to your parameters for better readability:

创建函数 create_food_type (
p_foodTypeName TEXT,
p_foodTypeIsActive BOOLEAN,
p_foodTypeCreatedBy TEXT,
p_foodTypeModifiedBy TEXT
)
返回表格 (operation TEXT, result JSON)
AS $$
DECLARE
operation text default 'existing';
BEGIN
IF NOT EXISTS (
SELECT *
FROM FoodType
WHERE FoodTypeName = p_foodTypeName
) THEN
operation = 'new';
INSERT INTO FoodType (FoodTypeName, FoodTypeIsActive, FoodTypeCreatedDate, FoodTypeCreatedBy, FoodTypeModifiedDate, FoodTypeModifiedBy)
VALUES (p_foodTypeName, p_foodTypeIsActive, CURRENT_TIMESTAMP, p_foodTypeCreatedBy, CURRENT_TIMESTAMP, p_foodTypeModifiedBy);
END IF;
RETURN QUERY (
SELECT operation, json_build_object(
'FoodTypeId', FoodTypeID,
'FoodTypeName', FoodTypeName,
'FoodTypeIsActive', FoodTypeIsActive,
'FoodTypeCreatedDate', FoodTypeCreatedDate,
'FoodTypeCreatedBy', FoodTypeCreatedBy,
'FoodTypeModifiedDate', FoodTypeModifiedDate,
'FoodTypeModifiedBy', FoodTypeModifiedBy
)
FROM FoodType
WHERE FoodTypeName = p_foodTypeName
LIMIT 1
);
END;
$$ LANGUAGE plpgsql;

英文:

The body language should be plpgsql, also add suffix to your parameters for better readability :

CREATE FUNCTION create_food_type (
  p_foodTypeName TEXT,
  p_foodTypeIsActive BOOLEAN,
  p_foodTypeCreatedBy TEXT,
  p_foodTypeModifiedBy TEXT
)
RETURNS TABLE (operation TEXT, result JSON)
AS $$
DECLARE
  operation text default 'existing';
BEGIN
  IF NOT EXISTS (
    SELECT *
    FROM FoodType
    WHERE FoodTypeName = p_foodTypeName
  ) THEN
      operation = 'new';
      INSERT INTO FoodType (FoodTypeName, FoodTypeIsActive, FoodTypeCreatedDate, FoodTypeCreatedBy, FoodTypeModifiedDate, FoodTypeModifiedBy)
      VALUES (p_foodTypeName, p_foodTypeIsActive, CURRENT_TIMESTAMP, p_foodTypeCreatedBy, CURRENT_TIMESTAMP, p_foodTypeModifiedBy);
  END IF;
  RETURN QUERY (
      SELECT operation, json_build_object(
        'FoodTypeId', FoodTypeID,
        'FoodTypeName', FoodTypeName,
        'FoodTypeIsActive', FoodTypeIsActive,
        'FoodTypeCreatedDate', FoodTypeCreatedDate,
        'FoodTypeCreatedBy', FoodTypeCreatedBy,
        'FoodTypeModifiedDate', FoodTypeModifiedDate,
        'FoodTypeModifiedBy', FoodTypeModifiedBy
      )
      FROM FoodType
      WHERE FoodTypeName = p_foodTypeName
      LIMIT 1
    );
END;
$$ LANGUAGE plpgsql;

huangapple
  • 本文由 发表于 2023年5月15日 00:01:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76248429.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定