Building where clause based upon parameters

Question

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.

Answer 1

Your suggestion is bad practice

From the code given it looks like you want to

• supply a variable from your application $1
• depending on that supplied variable make sure column fld1 is set to a specific set of values.
• I like the suggestion of using simple OR however, if your condition is complex and you want to maintain it that can get to be very messy. Here is another way.

Here was your code,

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

However, I would unroll this as a JOIN, and later spring it off into another table. Really CASE should likely never be used in a WHERE clause.

select fld1, count(fld1)
from xyz
join ( VALUES
  (1, ARRAY['Value1'])
  , (2, ARRAY['Value2'])
  , (3, ARRAY['Value1','Value2'])
  , (4, ARRAY['Value4','Value5','Value6'])
) AS cond(code,values)
  ON ( code = $1 AND fld1 = any(cond.values) )
group by fld1

From there you can even use CTE's which may make it faster, and it'll look more maintainable.

WITH cond(code,values) AS (VALUES
  (1, ARRAY['Value1'])
  , (2, ARRAY['Value2'])
  , (3, ARRAY['Value1','Value2'])
  , (4, ARRAY['Value4','Value5','Value6'])
)
select fld1, count(fld1)
from xyz
join cond ON ( code = $1 AND fld1 = any(cond.values) )
group by fld1

From the CTE, you can just as well make cond(fld1,values) its own table if it gets too bloody.

Answer 2

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 ;

Answer 3

Using arrays:

select fld1, count(fld1)
from xyz
where fld1 = any(
    case $1
        when 1 then array('Value1')
        when 2 then array('Value2')
        when 3 then array('Value1','Value2')
        when 4 then array('Value4','Value5','Value6')
        else NULL -- actually can be skipped as mentioned by ypercubeᵀᴹ
    end 
)
group by fld1;

if you execute explain verbose select 1 in (1,2,3); then you will see that the in operator translated to ... = any(... internally:

╔════════════════════════════════════════════╗
║                 QUERY PLAN                 ║
╠════════════════════════════════════════════╣
║ Result  (cost=0.00..0.01 rows=1 width=0)   ║
║   Output: (1 = ANY ('{1,2,3}'::integer[])) ║
╚════════════════════════════════════════════╝

Answer 4

Since implementing the filter with the help of a virtual table is an option for you, you could also consider the classic one-to-many form instead of arrays:

values
  (1, 'Value1'),
  (2, 'Value2'),
  (3, 'Value1'),
  (3, 'Value2'),
  (4, 'Value4'),
  (4, 'Value5'),
  (4, 'Value6')

You can use it in a number of ways:

• join it conventionally:

  select
    xyz.fld1,
    count(xyz.fld1)
  from
    xyz
    inner join
    (
      values
        (1, 'Value1'),
        (2, 'Value2'),
        (3, 'Value1'),
        (3, 'Value2'),
        (4, 'Value4'),
        (4, 'Value5'),
        (4, 'Value6')
    ) as fltr (paramValue, fld1Value)
    on ($1, xyz.fld1) = (fltr.paramValue, fltr.fld1Value)
  ;
  

• use it as the right side of an IN predicate:

  select
    xyz.fld1,
    count(xyz.fld1)
  from
    xyz
  where
    ($1, xyz.fld1) in
    (
      values
        (1, 'Value1'),
        (2, 'Value2'),
        (3, 'Value1'),
        (3, 'Value2'),
        (4, 'Value4'),
        (4, 'Value5'),
        (4, 'Value6')
    )
  ;
  

• intersect it with each source row, checking the matches in an EXISTS predicate:

  select
    xyz.fld1,
    count(xyz.fld1)
  from
    xyz
  where
    exists
    (
      values
        ($1, xyz.fld1)
      intersect
      values
        (1, 'Value1'),
        (2, 'Value2'),
        (3, 'Value1'),
        (3, 'Value2'),
        (4, 'Value4'),
        (4, 'Value5'),
        (4, 'Value6')
    )
  ;