Postgres:将自定义类型从Java传递到postgres函数 [英] Postgres: Passing custom types from Java to postgres function

问题描述

我有一个postgres函数，该函数将复杂的用户定义类型作为输入.

I have a postgres function which takes a complex user defined type as an input.

CREATE OR REPLACE FUNCTION addUser(user IN O_USER) RETURNS VOID
AS
$$
BEGIN

end;
$$ language plpgsql;

CREATE TYPE O_USER AS (
  id NUMERIC,
  name VARCHAR(50),
  addresses O_ADDRESS[]
);


CREATE TYPE O_ADDRESS AS (
  id NUMERIC,
  city VARCHAR(50)
);

现在我需要从Java调用该函数. 我了解的是，我需要传递一个字符串，该字符串将由db类型转换为UDT.

Now I need to call the function from Java. What I understood is that I need to pass a String which will be typecasted to the UDT by db.

当我传递没有地址的用户时，则可以正常工作.但是，如果我也通过地址数组，那么它给了我错误.

When I am passing the user without the address, then its working fine. But if I pass the array of address as well, then it is giving me the error.

ERROR:  malformed array literal: "{(1"
DETAIL:  Unexpected end of input.
SQL state: 22P02
Character: 23

我也可以使用postgres select语句重现该问题.例如，以下方法效果很好

I could reproduce the issue with the postgres select statement as well. For example following works fine

Select * from addUser('(1, "Vishal", {})');

但是当我通过地址时，这是一个问题

But when I pass the addresses, then its a problem

Select * from addUser('(1, "Vishal", {(1,"tet")})');
Select * from addUser('(1, "Vishal", {"(1,)"})');
Select * from addUser('(1, "Vishal", {"(1,null)"})')

我相信我无法正确构造字符串.知道这里有什么问题吗?

I believe I am not able to construct the string properly. Any idea what is wrong here?

更新

调用该函数的Java代码

Java code to call the function

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

import javax.sql.DataSource;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

public class AddUserProcedurePostgres extends StoredProcedure {

    public AddUserProcedurePostgres(DataSource dataSource) {
        super(dataSource, "addUser");
        setFunction(true);

        declareParameter(new SqlParameter("i_User", Types.OTHER, "O_USER"));

        compile();
    }

    public void execute() {

        Map<String, Object> input = new HashMap<>();
        input.put("i_User", "(1, Vishal, array[(1,tet)]::O_ADDRESS[])");

        execute(input);
    }

}

jdbc返回的错误是:

Error returned by the jdbc is:

Caused by: org.postgresql.util.PSQLException: ERROR: malformed array literal: " array[(1"
  Detail: Array value must start with "{" or dimension information.

推荐答案

我发现了两种将所需值传递给函数的方法:

I found 2 ways to pass the required value to the function:

1. 如果UDT的字符串很复杂，则很难创建它.最简单的方法是进行逆向工程.在plpgsql中创建UDT并进行打印.这样，您将获得需要从Java传递的字符串.现在，用Java代码编写一个逻辑来创建这样的字符串.
2. 另一种方法是以json格式和函数传递值，解析json并自行构造UDT.

我选择第二种方法是因为它易于维护.

I choose the 2nd approach as it is easy to maintain.

这篇关于Postgres:将自定义类型从Java传递到postgres函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！