CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1case-when
Danny Petrunov
Danny Petrunov

Reputation: 101

PostgreSQL function multiple rows returned from subquery in case statement

So I have written a postgreSQL function that is supposed to do a search on a table based on a huge amount of optional input parameters which i group with lots of AND statements. This one however:

AND
(
 (newcheck IS NULL) 
    OR
    ( 
    newcheck IS NOT NULL AND product.id IN(
    CASE WHEN newcheck='New' 
        THEN
        (SELECT product.id FROM product WHERE product.anew IS true)
    ELSE
        (SELECT product.id from product WHERE product.anew IS false)
    END)
    )
)

gives me a 

ERROR:  more than one row returned by a subquery used as an expression

This isnt helping much since I do want it to return a lot more than one row. The values of the newcheck variable will be sent from a dropdown menu in a web form so it can only be 'New' or 'Old'. Any ideas on what might be causing this problem?

Upvotes: 0

Views: 2125

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28641

Try something like:

AND ((newcheck IS NULL)
     OR (newcheck IS NOT NULL 
         AND product.id IN (SELECT product.id 
                            FROM product 
                            WHERE product.anew = CASE WHEN newcheck='New' 
                                                      THEN true 
                                                      ELSE false
                                                 END))

Upvotes: 1

Related Questions