英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论