Postgresql – Building where clause based upon parameters

casepostgresqlwhere

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.

Best Answer

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.

Related Question