How to use Case statement in Postgresql?-CodePudding

This is my SQL Query

CASE WHEN (1>2) THEN(
    select * from rate limit 10
    )
    ELSE
    (
    select * from rate limit 1
    )

When I use Case statement like above , I can get error like below.

ERROR:  syntax error at or near "CASE"
LINE 2: CASE WHEN (1>2) THEN(
        ^
SQL state: 42601
Character: 2

Can anyone help me to solve this

CodePudding user response：

Put a SELECT in front of the CASE statement. Also, you need an END after the last statement of the CASE. You need a place for the result of the CASE expression to be stored. The examples in the documentation are not executing statements that return a value; just variable assignment. So you don't need a SELECT there. If you don't care about the return from the overall query, you can use PERFORM instead of SELECT.

SELECT CASE 
       WHEN 1 > 2 -- always false
       THEN (SELECT * FROM rate LIMIT 10)
       ELSE (SELECT * FROM rate LIMIT 1)
        END
;

You can also assign the result of the subqueries to a variable if this CASE is defined inside of a function.

DO
$$
DECLARE
    rec RECORD;
BEGIN
    CASE WHEN 1 > 2
    THEN (SELECT * INTO rec FROM rate LIMIT 10)
    ELSE (SELECT * INTO rec FROM rate LIMIT 1)
     END
    ;
END;
$$ LANGUAGE PLPGSQL;