SQL Postgres 嵌套 if in case 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4198238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres nested if in case query
提问by BvuRVKyUVlViVIc7
Could you tell my why the following isnt working in postgres sql?:
你能告诉我为什么以下在 postgres sql 中不起作用吗？：
See updated code below
UPDATE:
更新：
I expect the query to return "0.30" as float. This construct is only for testing purposes, i have some complex querys which depend on this conditional structure... BUt i dont know how to fix it..
我希望查询以浮点数形式返回“0.30”。此构造仅用于测试目的，我有一些依赖于此条件结构的复杂查询......但我不知道如何修复它......
Result is:
结果是：
ERROR: syntax error at or near "1"
LINE 4: if 1=1 then
UPDATE:
更新：
This construction appears in a function... so I want to do following:
这个构造出现在一个函数中......所以我想做以下事情：
CREATE FUNCTION f_test(myvalue integer) RETURNS float AS $$
BEGIN
select (
case (select '1')
when '1' then
if 1=1 then
0.30::float
else
0.50::float
end
else
1.00::float
end
);
END;
$$ LANGUAGE plpgsql;
select f_test(1) as test;
Error message see above.
错误信息见上。
回答by AndreKR
There is no IF expr THEN result ELSE result ENDsyntax for normal SQL queries in Postgres. As there is neither an IF()function as in MySQL, you have to use CASE:
IF expr THEN result ELSE result ENDPostgres 中没有普通 SQL 查询的语法。由于没有IF()MySQL 中的函数，您必须使用CASE：
select (
case (select '1')
when '1' then
case when 1=1 then 0.30::float else 0.50::float end
else
1.00::float
end
);
回答by jmz
I don't know what you're trying to achieve with this function, but here's a working version.
我不知道你想用这个功能实现什么，但这是一个工作版本。
CREATE FUNCTION f_test(myvalue integer) RETURNS float AS $$
BEGIN
IF myvalue = 1 THEN
IF 1=1 THEN
RETURN 0.30::FLOAT;
ELSE
RETURN 0.50::FLOAT;
END IF;
ELSE
RETURN 1.0::FLOAT;
END IF;
END;
The function returns 0.3 if input value is 1, otherwise it'll return 1. Edit:Note that 0.5 is never returned by the function.
如果输入值为 1，该函数返回 0.3，否则返回 1。编辑：请注意，该函数从不返回 0.5。
