Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres nested if in case query

Could you tell my why the following isnt working in 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..

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.

like image 342
BvuRVKyUVlViVIc7 Avatar asked Nov 16 '10 19:11

BvuRVKyUVlViVIc7


1 Answers

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
);
like image 79
AndreKR Avatar answered Oct 19 '22 23:10

AndreKR