If I pass in NULL for xyz, I want to get all the rows.

If I set xyz to a value, I only want the rows in the table where the xyz column matches the value I pass in.

And I don’t want to do build up a string in my app code.

Here is how:


select *
from my_table
where
case
when %(xyz)s is null then true
when %(xyz)s = xyz then true
else false
end
;


And here’s a slight tweak if you want to pass in an array of allowed values:


select *
from my_table
where
case
when %(xyz)s is null then true
when xyz = any(%(xyz)s) then true
else false
end
;


I hope this helps! If you know a better way, let me know!