将List作为参数传递到postgres函数中

Question

我有如下spring数据存储库界面：

public interface MyEntityRepository extends 
        JpaRepository<MyEntity, Long> {

    @Query(nativeQuery = true, value = "select * from my_func(:myList)")
    Page<MyEntity> findBy(
            @NonNull @Param("myList") List<String> myList,
            @NonNull Pageable pageable);

}

Postgres的函数我定义如下（但如果我做错了，我可以更改）：

CREATE OR REPLACE FUNCTION my_func(variadic myList text[])
RETURNS SETOF myEntityTable AS $$
... some logic
select * from myEntityTable t where t.foo in (myList);

调用此存储库方法时，出现以下错误：

ERROR: operator does not exist: character varying = text[]
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Where: PL/pgSQL function f_najdi_autorizaciu_na_spracovanie(text[]) line 28 at RETURN QUERY

你能告诉我在postgres函数中应该使用什么类型吗？谢谢你的建议。

编辑：我不能使用本机查询上述存储库方法并将其传递到IN子句中，因为我在DB函数中有更多的逻辑、变量等。。。它必须是DB函数。

Answer 1

不确定这是否会增加很多价值，我只是希望它能有所帮助。

PostgreSQL函数期望从列表中作为输入的最终格式为以下模式，使用数组：

select my_func(array['item1','item2']::my_type[]);

请参阅数据库管理员的完整示例。SE.

就你而言：

select my_func(array['item1','item2']::text[]);

我在自己的测试中发现

select my_func(array['item1','item2']);

应该已经足够了。

然后，唯一的目标是从原始java类型中获取此格式。另一个问题回答了这个问题。这个答案只是为了说明这一切的目的。

Answer 2

恐怕我不知道如何使用Spring Data JPA来实现这一点，但是使用普通的JDBC，您必须简单地将bind变量转换为text[]，并传递一个String[]类型而不是列表。例如：

try (PreparedStatement s = conn.prepareStatement("select * from my_func(?::text[])")) {
    s.setObject(1, myList.toArray(new String[0]));

    try (ResultSet rs = s.executeQuery()) {
        // ...
    }
}

这里的关键信息是，JDBC驱动程序需要的是数组，而不是列表。

Answer 3

我在类似情况下使用了以下变通解决方案：

1） 创建了两个助手函数：

-- Convert a variable number of text arguments to text array
-- Used to convert Java collection to the text array
--
create or replace function list_to_array(variadic _list text[]) returns text[] language sql as $$
select _list;
$$;

-- Convert the bytea argument to null.
-- Used to convert Java null to PostgreSQL null
--
create or replace function list_to_array(_list bytea) returns text[] language sql as $$
select null::text[];
$$;

2） 在主功能中使用any而不是中的，例如：

create or replace function my_func(_params text[]) 
returns table (field1 text, field2 text) 
language sql as 
$$
select
  t.field1 as field1,
  t.field2 as field2,
from
  my_table t
where
  array_length(_params, 1) is null or t.foo = any(_params);
$$;

3）然后在存储库方法中使用它们，例如：

@NonNull
@Query(value = "select ... from my_func(list_to_array(?1))", nativeQuery = true)
List<MyProjection> getFromMyFunc(@Nullable Set<String> params, @NonNull Pageable page);