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.

• ypercubeᵀᴹ: I slightly disagree on the "bad practice". His intention is bad practice. His code is just bad code because it doesn't do what he was hoping to do. ("Values 3 and 4 fail not syntactically but no rows are returned.")

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 ypercubeTM
 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')
   )
  ;