PostgreSQL BIGINT[]参数函数在Spring Boot调用失败问题

我在PostgreSQL 14中编写了一个PL/pgSQL函数，功能如下：

• 接收BIGINT[]类型输入参数；
• 扫描自定义订单表myorder，检查传入的订单ID是否存在；
• 返回包含所有无效订单ID的BIGINT[]数组。

函数代码如下：

CREATE OR REPLACE FUNCTION myschema.myFunction(
order_list bigint[])
RETURNS bigint[]
LANGUAGE 'plpgsql'
AS $BODY$
declare
  order_number BIGINT;
  order_count integer;
  invalid_order_number_arr BIGINT[];
begin
    FOREACH order_number IN ARRAY order_list                             
     LOOP                                                     
         Select count(*) into order_count from  
          myorder ord where ord.id=order_number;
    
        IF order_count = 0 THEN
           invalid_order_number_arr := ARRAY_APPEND(invalid_order_number_arr,order_number);
        END IF;     
     END LOOP;
    RETURN invalid_order_number_arr;
   end;
$BODY$;

我在Spring Boot中通过JPA原生查询调用该函数，代码如下：

@Query(nativeQuery = true,value = "Select myFunction(:orderIds)")
public Set<Long> getFailureOrderIds(@Param("orderIds") Set<Long> orderIds);

数据库连接配置正常，启动无问题，但调用该方法时抛出PSSqlException，提示myFunction(param)不存在。该函数在PgAdmin中测试正常，想确认：Java侧是否无法将java.util.Set作为参数传递给接收数组类型的PL/pgSQL函数？

1. 核心问题：参数类型不匹配 #

PostgreSQL的数组类型无法与Java的Set<Long>自动映射。JPA默认不会将Set转换为PostgreSQL的bigint[]类型，导致数据库接收到的参数类型与函数定义不匹配，最终触发"函数不存在"的错误（本质是参数签名不匹配）。

2. 具体修复步骤 #

（1）调整Java参数类型并显式转换数组

将方法参数改为Long[]，同时在JPA查询中明确将参数转为PostgreSQL数组类型，还要指定函数所属的schema：

@Query(nativeQuery = true, value = "SELECT myschema.myFunction(CAST(:orderIds AS bigint[]))")
public Set<Long> getFailureOrderIds(@Param("orderIds") Long[] orderIds);

如果业务代码中必须使用Set，调用时先将其转为数组即可：

Set<Long> orderSet = ...;
Set<Long> invalidOrders = repo.getFailureOrderIds(orderSet.toArray(new Long[0]));

（2）确保函数schema被正确引用

你的函数定义在myschema下，但原JPA查询只写了myFunction，若当前数据库用户的默认schema不是myschema，就会找不到函数。必须在查询中明确指定schema路径。

（3）优化PL/pgSQL函数性能（可选）

原函数用循环逐个查询效率极低，可改为集合运算的方式优化：

CREATE OR REPLACE FUNCTION myschema.myFunction(
order_list bigint[])
RETURNS bigint[]
LANGUAGE 'plpgsql'
AS $BODY$
begin
    RETURN ARRAY(
        SELECT unnest(order_list)
        EXCEPT
        SELECT id FROM myorder
    );
end;
$BODY$;

这种写法直接通过集合差集找出不存在的ID，批量处理效率远高于循环查询。

3. 验证要点 #

• 确认数据库用户有权限访问myschema.myFunction；
• 在PgAdmin中用数组参数测试函数，比如SELECT myschema.myFunction(ARRAY[123,456]::bigint[])，确保函数本身正常；
• 调试时查看JPA生成的SQL语句，确认参数是否被正确转换为PostgreSQL数组。

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