Postgres nested if in case query

sql postgresql if-statement case

25,821

Solution 1

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
);

Solution 2

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.

25,821

Author by

BvuRVKyUVlViVIc7

SOreadytohelp

Updated on July 19, 2022

Comments

BvuRVKyUVlViVIc7 almost 2 years
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.
BvuRVKyUVlViVIc7 over 13 years

Yes, see postgresql.org/docs/8.4/static/plpgsql-control-structures.ht‌ml
AndreKR over 13 years

No no, that is for stored procedures. For them there is one in MySQL, too, but not for normal SQL.
BvuRVKyUVlViVIc7 over 13 years

Ive updated my post, i use this code in a function... so it should work?
AndreKR over 13 years

No, you are still inside the SELECT. These are the conditional expressions you can use in normal queries: postgresql.org/docs/8.4/static/functions-conditional.html
BvuRVKyUVlViVIc7 over 13 years

It only a test function without sense.. But I have complex queries which depend on this conditional structure.. I will try your answer... THanks
Aaron almost 12 years

This confused me for a while too. PostgreSQL has an IF control statement, however, it is only for use inside functions. For queries, you MUST use CASE.
Stew-au almost 10 years

Upvoted for showing me how to nest multiple CASE statements in postgres.