Building where clause based upon parameters

In PostgreSQL I am trying to build a where clause in a function that uses an inbound parameter to determine the contents of the IN

For example:

select fld1, count(fld1)
from xyz
where fld1 in (
    case $1
        when 1 then 'Value1'
        when 2 then 'Value2'
        when 3 then '''Value1'',''Value2'''
        when 4 then '''Value4'',''Value5'',''Value6'''
    else NULL
)
group by fld1

Value 1 works fine as does Value2, Values 3 and 4 fail not syntactically but no rows are returned.

The output of a CASE expression has to be a single value not a list of values.

The statement did not give a syntax error for cases 3 and 4 because '''Value1'',''Value2''' is evaluated as a single string (which includes 4 quote characters and a comma): 'Value1','Value2'

You could rewrite your condition using a CASE expression that evaluates to boolean, like this:

select fld1, count(*)
from xyz
where case $1
          when 1 then fld1 in ('Value1')
          when 2 then fld1 in ('Value2')
          when 3 then fld1 in ('Value1', 'Value2')
          when 4 then fld1 in ('Value4', 'Value5', 'Value6')
      end
group by fld1 ;

but I'd prefer to write it more more simply using OR:

select fld1, count(*)
from xyz
where ( $1 = 1 and fld1 in ('Value1')
     or $1 = 2 and fld1 in ('Value2')
     or $1 = 3 and fld1 in ('Value1', 'Value2')
     or $1 = 4 and fld1 in ('Value4', 'Value5', 'Value6')
      )
group by fld1 ;

StackExchange Database Administrators Q#155499, answer score: 5