如果以防查询，Postgres嵌套 [英] Postgres nested if in case query

问题描述

你能告诉我为什么以下不能在postgres sql中工作吗？：

Could you tell my why the following isnt working in postgres sql?:

See updated code below

更新：

我希望查询返回0.30作为浮动。
这个构造仅用于测试目的，我有一些复杂的查询依赖于这个条件结构... BUt我不知道如何解决它..

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..

结果是：

ERROR:  syntax error at or near "1"
LINE 4:     if 1=1 then

更新：

这种结构出现在函数......所以我想做以下事情：

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;

错误信息见上文。

推荐答案

没有 IF expr THEN结果ELSE结果END Postgres中正常SQL查询的语法。因为在MySQL中既没有 IF（）函数，你必须使用 CASE ：

There is no IF expr THEN result ELSE result END syntax for normal SQL queries in Postgres. As there is neither an IF() function as in MySQL, you have to use 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
);

这篇关于如果以防查询，Postgres嵌套的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！