How do you use ANY within a CASE statement?

Question

I have a Postgres query I'm working on that passes a parameter to the query through an API call. I've modified it to run locally in SQL Developer.

WHERE (string_to_array( name ,',')) =
CASE
    WHEN 0=1 THEN (string_to_array( name ,','))
    ELSE 
    ANY(string_to_array( 'John Smith,Steve Adam' ,','))
END

AND (string_to_array( role ,',')) =

CASE
    WHEN 0 = 1 THEN (string_to_array( Role ,','))
    ELSE 
    ANY(string_to_array( 'Manager,Analyst' ,','))
END;

When ran, I get the following error:

ERROR: syntax error at or near "ANY"

Anyone know why this is happening? Does Postgres not like my usage of ANY within the CASE statement? (Sorry if my formatting is bad)

Answer 1

The ANY() has to go next to the = operator.

Please try this:

WHERE (string_to_array( name ,',')) = ANY(
  CASE
    WHEN 0=1 THEN (string_to_array( name ,','))
    ELSE string_to_array( 'John Smith,Steve Adam' ,',')
  END
)

AND (string_to_array( role ,',')) = ANY(
  CASE
    WHEN 0 = 1 THEN (string_to_array( Role ,','))
    ELSE string_to_array( 'Manager,Analyst' ,',')
  END
);