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

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

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函数。

  1. CREATE FUNCTION create_food_type (
  2.   foodTypeName TEXT,
  3.   foodTypeIsActive BOOLEAN,
  4.   foodTypeCreatedBy INT,
  5.   foodTypeModifiedBy INT
  6. )
  7. RETURNS TABLE (operation TEXT, result JSON)
  8. LANGUAGE SQL
  9. AS $$
  10. BEGIN
  11.   IF EXISTS (
  12.     SELECT *
  13.     FROM "FoodType"
  14.     WHERE "FoodTypeName" = foodTypeName
  15.   ) THEN
  16.     -- 返回现有行和指示行已存在的标志
  17.     RETURN QUERY (
  18.       SELECT 'existing' AS operation, json_build_object(
  19.         'FoodTypeId', "FoodTypeID",
  20.         'FoodTypeName', "FoodTypeName",
  21.         'FoodTypeIsActive', "FoodTypeIsActive",
  22.         'FoodTypeCreatedDate', "FoodTypeCreatedDate",
  23.         'FoodTypeCreatedBy', "FoodTypeCreatedBy",
  24.         'FoodTypeModifiedDate', "FoodTypeModifiedDate",
  25.         'FoodTypeModifiedBy', "FoodTypeModifiedBy"
  26.       )
  27.       FROM "FoodType"
  28.       WHERE "FoodTypeName" = foodTypeName
  29.       LIMIT 1
  30.     );
  31.   ELSE
  32.     -- 插入新行并返回新插入的行和指示新行已添加的标志
  33.     RETURN QUERY (
  34.       INSERT INTO "FoodType" ("FoodTypeName", "FoodTypeIsActive", "FoodTypeCreatedDate", "FoodTypeCreatedBy", "FoodTypeModifiedDate", "FoodTypeModifiedBy")
  35.       VALUES (foodTypeName, foodTypeIsActive, CURRENT_TIMESTAMP, foodTypeCreatedBy, CURRENT_TIMESTAMP, foodTypeModifiedBy)
  36.       RETURNING 'new' AS operation, json_build_object(
  37.         'FoodTypeId', "FoodTypeID",
  38.         'FoodTypeName', "FoodTypeName",
  39.         'FoodTypeIsActive', "FoodTypeIsActive",
  40.         'FoodTypeCreatedDate', "FoodTypeCreatedDate",
  41.         'FoodTypeCreatedBy', "FoodTypeCreatedBy",
  42.         'FoodTypeModifiedDate', "FoodTypeModifiedDate",
  43.         'FoodTypeModifiedBy', "FoodTypeModifiedBy"
  44.       )
  45.     );
  46.   END IF;
  47. END;
  48. $$;

I have tried CASE WHEN as well.

我也尝试过使用CASE WHEN。

  1.  IF EXISTS (
  2.     SELECT *
  3.     FROM "FoodType"
  4.     WHERE "FoodTypeName" = foodTypeName
  5.   ) THEN
  6. --Do Something
  7.  ELSE
  8. --Do Something
  9.  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.

  1. CREATE FUNCTION create_food_type (
  2.   foodTypeName TEXT,
  3.   foodTypeIsActive BOOLEAN,
  4.   foodTypeCreatedBy INT,
  5.   foodTypeModifiedBy INT
  6. )
  7. RETURNS TABLE (operation TEXT, result JSON)
  8. LANGUAGE SQL
  9. AS $$
  10. BEGIN
  11.   IF EXISTS (
  12.     SELECT *
  13.     FROM "FoodType"
  14.     WHERE "FoodTypeName" = foodTypeName
  15.   ) THEN
  16.     -- Return the existing row and flag indicating row already exists
  17.     RETURN QUERY (
  18.       SELECT 'existing' AS operation, json_build_object(
  19.         'FoodTypeId', "FoodTypeID",
  20.         'FoodTypeName', "FoodTypeName",
  21.         'FoodTypeIsActive', "FoodTypeIsActive",
  22.         'FoodTypeCreatedDate', "FoodTypeCreatedDate",
  23.         'FoodTypeCreatedBy', "FoodTypeCreatedBy",
  24.         'FoodTypeModifiedDate', "FoodTypeModifiedDate",
  25.         'FoodTypeModifiedBy', "FoodTypeModifiedBy"
  26.       )
  27.       FROM "FoodType"
  28.       WHERE "FoodTypeName" = foodTypeName
  29.       LIMIT 1
  30.     );
  31.   ELSE
  32.     -- Insert a new row and return the newly inserted row and flag indicating new row added
  33.     RETURN QUERY (
  34.       INSERT INTO "FoodType" ("FoodTypeName", "FoodTypeIsActive", "FoodTypeCreatedDate", "FoodTypeCreatedBy", "FoodTypeModifiedDate", "FoodTypeModifiedBy")
  35.       VALUES (foodTypeName, foodTypeIsActive, CURRENT_TIMESTAMP, foodTypeCreatedBy, CURRENT_TIMESTAMP, foodTypeModifiedBy)
  36.       RETURNING 'new' AS operation, json_build_object(
  37.         'FoodTypeId', "FoodTypeID",
  38.         'FoodTypeName', "FoodTypeName",
  39.         'FoodTypeIsActive', "FoodTypeIsActive",
  40.         'FoodTypeCreatedDate', "FoodTypeCreatedDate",
  41.         'FoodTypeCreatedBy', "FoodTypeCreatedBy",
  42.         'FoodTypeModifiedDate', "FoodTypeModifiedDate",
  43.         'FoodTypeModifiedBy', "FoodTypeModifiedBy"
  44.       )
  45.     );
  46.   END IF;
  47. END;
  48. $$;

I have tried CASE WHEN as well.

  1.  IF EXISTS (
  2.     SELECT *
  3.     FROM "FoodType"
  4.     WHERE "FoodTypeName" = foodTypeName
  5.   ) THEN
  6. --Do Something
  7.  ELSE
  8. --Do Something
  9.  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 :

  1. CREATE FUNCTION create_food_type (
  2.   p_foodTypeName TEXT,
  3.   p_foodTypeIsActive BOOLEAN,
  4.   p_foodTypeCreatedBy TEXT,
  5.   p_foodTypeModifiedBy TEXT
  6. )
  7. RETURNS TABLE (operation TEXT, result JSON)
  8. AS $$
  9. DECLARE
  10.   operation text default 'existing';
  11. BEGIN
  12.   IF NOT EXISTS (
  13.     SELECT *
  14.     FROM FoodType
  15.     WHERE FoodTypeName = p_foodTypeName
  16.   ) THEN
  17.       operation = 'new';
  18.       INSERT INTO FoodType (FoodTypeName, FoodTypeIsActive, FoodTypeCreatedDate, FoodTypeCreatedBy, FoodTypeModifiedDate, FoodTypeModifiedBy)
  19.       VALUES (p_foodTypeName, p_foodTypeIsActive, CURRENT_TIMESTAMP, p_foodTypeCreatedBy, CURRENT_TIMESTAMP, p_foodTypeModifiedBy);
  20.   END IF;
  21.   RETURN QUERY (
  22.       SELECT operation, json_build_object(
  23.         'FoodTypeId', FoodTypeID,
  24.         'FoodTypeName', FoodTypeName,
  25.         'FoodTypeIsActive', FoodTypeIsActive,
  26.         'FoodTypeCreatedDate', FoodTypeCreatedDate,
  27.         'FoodTypeCreatedBy', FoodTypeCreatedBy,
  28.         'FoodTypeModifiedDate', FoodTypeModifiedDate,
  29.         'FoodTypeModifiedBy', FoodTypeModifiedBy
  30.       )
  31.       FROM FoodType
  32.       WHERE FoodTypeName = p_foodTypeName
  33.       LIMIT 1
  34.     );
  35. END;
  36. $$ LANGUAGE plpgsql;

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

发表评论

匿名网友

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

确定