PostgreSQL中如何为params参数设置多个值适配IN查询场景

问题原因 #

原有CTE中upnikid定义为double precision类型的标量值，IN (upnikid)语法仅支持匹配单个值。直接传入逗号分隔的多值会触发类型不匹配错误：PostgreSQL不会自动将逗号分隔的文本解析为值集合，标量类型本身也无法承载多值、全量匹配的逻辑。

实现方式 #

将upnikid参数替换为double precision[]数组类型，通过数组操作符实现三种传参模式的兼容：

• 单值匹配：传入单元素数组，例如ARRAY[141]::double precision[]
• 多值匹配：传入多元素数组，例如ARRAY[141,1000]::double precision[]
• 全量匹配/空值匹配：传入空数组ARRAY[]::double precision[]或NULL，逻辑上跳过该字段过滤

修改后完整代码示例 #

with 
  params as (
    select 
      '2018-06-01'::timestamp p_datum_vlozitve_from, 
      '2019-01-01'::timestamp p_datum_vlozitve_to, 
      0::double precision glavnica_od, 
      141::double precision glavnica_do,
      -- 原单值参数改为数组类型，按业务需求传入对应值即可
      ARRAY[141,1000]::double precision[] upnik_ids
  )
select 
  -- 此处保留主查询原有查询字段
  left join (
    select paketi.id_upnik, sum(specifikacije_postavke.placilo) as pokpravdnistroski
    from specifikacije_postavke, specifikacije1, dolzniki_terjatve, paketi, params
    where 
      specifikacije1.idizracun=specifikacije_postavke.idizracun 
      and dolzniki_terjatve.referenca=specifikacije1.referenca 
      and paketi.id_paket=dolzniki_terjatve.id_paket
      and postavkastroski='4' 
      and obresti=false
      and dolzniki_terjatve.glavnica > glavnica_od 
      and dolzniki_terjatve.glavnica < glavnica_do
      and dolzniki_terjatve.datum_vlozitve >= p_datum_vlozitve_from 
      and dolzniki_terjatve.datum_vlozitve < p_datum_vlozitve_to
      -- 替换原IN条件，兼容三种传参模式
      and (
        coalesce(array_length(upnik_ids, 1), 0) = 0 
        or paketi.id_upnik = any(upnik_ids)
      )
      and datumplacila <= date(dolzniki_terjatve.datum_vlozitve) + interval '1 month' 
    group by paketi.id_upnik
  ) as tabelapravdnistroski on paketi.id_upnik=tabelapravdnistroski.id_upnik

逻辑说明 #

• 传入单元素数组时，paketi.id_upnik = any(upnik_ids)等价于等值匹配，和原有单值查询逻辑完全一致
• 传入多元素数组时，=any(upnik_ids)会匹配所有包含在数组内的id，实现多值过滤效果
• 传入空数组或NULL时，数组长度判断条件成立，直接跳过id过滤，返回所有符合其他条件的记录，实现全量匹配

内容的提问来源于stack exchange，提问作者rokfamer