提问人：Kyle 提问时间：11/24/2022 最后编辑：Kyle 更新时间：11/24/2022 访问量：45
PostgreSQL CASE 语句语法问题
PostgreSQL CASE statement syntax issues
问：
我收到以下语法错误：
CREATE OR REPLACE FUNCTION my_function(old_num INTEGER)
returns INTEGER
language plpgsql
AS
$$
DECLARE new_num INTEGER;
BEGIN
CASE
WHEN (old_num IN (1, 2, 3, 4)) THEN new_num = 10
WHEN (old_num IN (5, 6, 7, 8)) THEN new_num = 20
ELSE new_num = 0
END;
RETURN new_num;
END;
$$;
该错误指向第二个 WHEN。我试过使用各种括号的组合。这个语法有什么问题？？
答：
0赞
Thorsten Kettner
11/24/2022
#1
表达式生成一个值。您希望它产生 10、20 或 0，然后将其分配给变量 new_num。CASE
new_num :=
CASE
WHEN (old_num IN (1, 2, 3, 4)) THEN 10
WHEN (old_num IN (5, 6, 7, 8)) THEN 20
ELSE 0
END;
0赞
Stanislav Kovalenko
11/24/2022
#2
CREATE OR REPLACE FUNCTION my_function(old_num INTEGER)
returns INTEGER
language plpgsql
AS
$$
DECLARE new_num INTEGER;
BEGIN
new_num := (CASE
WHEN old_num in (1, 2, 3, 4) THEN 10
WHEN old_num in (5, 6, 7, 8) THEN 20
ELSE 0
END);
RETURN new_num;
END;
$$;
0赞
Adrian Klaver
11/24/2022
#3
备选解决方案：
CREATE OR REPLACE FUNCTION public.my_function(old_num integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE new_num INTEGER;
BEGIN
SELECT INTO new_num
CASE
WHEN (old_num IN (1, 2, 3, 4)) THEN 10
WHEN (old_num IN (5, 6, 7, 8)) THEN 20
ELSE 0
END;
RETURN new_num;
END;
$function$
select my_function(1);
my_function
-------------
10
select my_function(6);
my_function
-------------
20
select my_function(12);
my_function
-------------
0
0赞
Belayer
11/24/2022
#4
您可以将函数转换为 SQL 函数，并将其简化为单个 Select 语句。请参阅演示。
create or replace function public.my_function(old_num integer)
returns integer
language sql
as $$
select case when (old_num in (1, 2, 3, 4)) then 10
when (old_num in (5, 6, 7, 8)) then 20
else 0
end;
$$;
评论