从 jpa 中的本机查询中调用复合类型作为参数的函数 [英] Call a function with composite type as argument from native query in jpa

问题描述

我创建了一个postgres复合类型和函数如下

I have created a postgres composite type and function as follows

CREATE TYPE  test AS (a int, b timestamp, c int);

CREATE FUNCTION some_updates(t test[])
    begin
    // iterate t and perform operation
    end

select some_updates(CAST(array[(488,'2019-7-01',342)] AS test[])

以上函数和选择调用函数都可以正常工作.

The above function and select to call the function works properly.

在 spring jpa 中，我想调用函数并将test[]"的值作为来自本机查询的参数传递.但是它会导致错误.

In spring jpa I would want to call function and pass values for "test[]" as params from native query. However it results in error.

@Query(value="select * from some_updates(:myinput)")
Myclass getDetails(List<test> myinput);

我创建了一个 pojo 类测试如下

I have created a pojo class test as follows

class test
{
    int a;
    timestamp b;
    int c;

    //getters and setters
}

我怎么可能将值传递给 postgres 函数?

How can i possibly pass values to the postgres function?

推荐答案

传递数组 literal 而不是数组构造函数.该值可以作为字符串文字，然后被 Postgres 强制为正确的类型:

Pass an array literal instead of an array constructor. The value can be passed as string literal, then it is coerced to the right type by Postgres:

SELECT some_updates('{"(488, 2019-07-01 00:00:00,342)"}');

前提:函数没有被重载"，因此函数类型解析与无类型输入是明确的.

Precondition: the function is not "overloaded" so that function type resolution is unambiguous with untyped input.

相关:

• 如何将自定义类型数组传递给Postgres 函数
• 将自定义类型的数组从 node-pg 和 SQL 注入传递给 postgres 函数
• 有没有办法在 Postgres 中禁用函数重载

如果数组元素少于 100 个，VARIADIC 函数将有助于简化传递参数:

If you have fewer than 100 array elements, a VARIADIC function would help to simplify passing arguments:

CREATE FUNCTION some_updates(VARIADIC t test[]) ...

然后传递行文字，如:

SELECT some_updates('(488, 2019-07-01 00:00:00,342)'
                  , '(489, 2019-07-02 00:00:00,343)')

见:

• 返回匹配元素的行plpgsql 函数中的输入数组

如果您有(许多)更多行要传递，请考虑将它们写入带有普通 INSERT 的(临时)表并从那里处理它们.

If you have (many) more rows to pass, consider writing them to a (temporary) table with a plain INSERT and processing them from there.

这篇关于从 jpa 中的本机查询中调用复合类型作为参数的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！