Postgres nested if in case query

Question

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.

use proper language, no need to use anything beyond pg here. — Woot4Moo
– Woot4Moo, Commented Nov 16, 2010 at 19:43
define "isnt working". What did you expect to happen, and what actually happened. — Hamish
– Hamish, Commented Nov 16, 2010 at 19:43

AndreKR · Accepted Answer · 2010-11-16 20:12:31Z

9

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

edited Nov 16, 2010 at 20:12

answered Nov 16, 2010 at 19:58

AndreKR

34k21 gold badges120 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

BvuRVKyUVlViVIc7 Over a year ago

Yes, see postgresql.org/docs/8.4/static/plpgsql-control-structures.html

AndreKR Over a year ago

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

BvuRVKyUVlViVIc7 Over a year ago

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

AndreKR Over a year ago

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

Aaron Over a year ago

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.

|

jmz · Accepted Answer · 2010-11-16 20:08:21Z

3

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.

answered Nov 16, 2010 at 20:08

jmz

5,51730 silver badges30 bronze badges

1 Comment

BvuRVKyUVlViVIc7 Over a year ago

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

Collectives™ on Stack Overflow

Postgres nested if in case query

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related