PostgreSQL function multiple rows returned from subquery in case statement

Question

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?

The problem - every subselect returns more than one row and CASE statement expects one result in every branch. — Ihor Romanchenko
– Ihor Romanchenko, Commented Aug 8, 2013 at 8:39

Ihor Romanchenko · Accepted Answer · 2013-08-08 08:40:53Z

1

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

edited Aug 8, 2013 at 8:40

answered Aug 8, 2013 at 8:35

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

1 Comment

Danny Petrunov Over a year ago

Alright that seems to work perfectly when I set the newcheck to anything but 'New'. In the case where it is 'New' I only get one row as a result...

Collectives™ on Stack Overflow

PostgreSQL function multiple rows returned from subquery in case statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related