PostgreSQL中字符串数组转整数数组函数返回空问题排查

问题描述 #

编写的PL/pgSQL函数days_textArray_toIntArray调用时始终返回null，例如执行select days_textArray_toIntArray('{Wed,Sun}'::text[])时，预期返回[3,0]，实际得到空值。

原函数代码 #

create or replace function days_textArray_toIntArray(daysArray text[])
returns int[]
language plpgsql
set search_path = ''
as $$

declare

  textArray text[];
  intArray int[];

begin

  if ('Sun' = ANY(textArray)) then 
    intArray = array_append(intArray,0);
  elseif ('Mon' = ANY(textArray)) then 
    intArray = array_append(intArray,1);
  elseif ('Tue' = ANY(textArray)) then 
    intArray = array_append(intArray,2);
  elseif ('Wed' = ANY(textArray)) then 
    intArray = array_append(intArray,3);
  elseif ('Thu' = ANY(textArray)) then 
    intArray = array_append(intArray,4);
  elseif ('Fri' = ANY(textArray)) then 
    intArray = array_append(intArray,5);
  elseif ('Sat' = ANY(textArray)) then 
    intArray = array_append(intArray,6);
  end if;

  return intArray;

end;
$$

问题根源 #

1. 未使用输入参数：声明的textArray变量未赋值，始终为null，导致所有ANY(textArray)的条件判断都不成立，intArray保持初始null状态，最终返回null。
2. 无法处理多元素数组：使用elseif分支只会匹配第一个满足条件的项，即使修复变量问题，也只能返回单个元素的数组，无法处理输入数组中多个星期的情况。

修复方案 #

方案1：PL/pgSQL循环遍历实现 #

初始化空数组，遍历输入数组的每个元素，逐个映射为对应的整数：

create or replace function days_textArray_toIntArray(daysArray text[])
returns int[]
language plpgsql
set search_path = ''
as $$
declare
  intArray int[] := '{}'::int[]; -- 初始化为空数组而非null
  day text;
begin
  foreach day in array daysArray loop
    case day
      when 'Sun' then intArray := array_append(intArray, 0);
      when 'Mon' then intArray := array_append(intArray, 1);
      when 'Tue' then intArray := array_append(intArray, 2);
      when 'Wed' then intArray := array_append(intArray, 3);
      when 'Thu' then intArray := array_append(intArray, 4);
      when 'Fri' then intArray := array_append(intArray, 5);
      when 'Sat' then intArray := array_append(intArray, 6);
      -- 可选：处理无效输入，比如抛出错误或记录日志
      else raise notice '无效的星期字符串: %', day;
    end case;
  end loop;
  return intArray;
end;
$$;

方案2：SQL函数（更简洁高效） #

利用unnest展开数组，结合array_agg聚合结果：

create or replace function days_textArray_toIntArray(daysArray text[])
returns int[]
language sql
set search_path = ''
as $$
select array_agg(
  case day
    when 'Sun' then 0
    when 'Mon' then 1
    when 'Tue' then 2
    when 'Wed' then 3
    when 'Thu' then 4
    when 'Fri' then 5
    when 'Sat' then 6
    else null -- 可替换为默认值或抛出错误
  end
)
from unnest(daysArray) as day;
$$;

测试验证 #

执行select days_textArray_toIntArray('{Wed,Sun}'::text[])，会返回{3,0}，符合预期。

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