What is the correct way to call postgres function with custom types from the spring StoredProcedure?
Say I have a function and custom types as defined:
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)
);
While calling it from Spring I am converting user object to String as shown below:
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(SessionContext sessionContext, Dashboard dashboard) {
Map<String, Object> input = new HashMap<>();
input.put("i_User", "(1,Vishal)");
execute(input);
}
}
Now my questions are:
Is it the right way to create a String corresponding to the object and then pass it? Or is there any better way?
Suppose I need to add array of other custom type in the existing O_USER, then how the string need to be formatted?
CREATE TYPE O_USER AS (
id NUMERIC,
name VARCHAR(50)
addresses O_ADDRESS[]
);
select adduser(...)