Spring - calling stored function with complex result

Question

I have following type in PostgreSQL:

CREATE TYPE TR_PERSON AS (
i_out integer,
str_out text
);

Also I have stored function which returns my type:

CREATE OR REPLACE FUNCTION test_function(id int)
RETURNS TR_PERSON
AS $$
SELECT $1, text('Alice')
$$ LANGUAGE SQL;

I'm trying to use SimpleJdbcCall from spring to get data from DB:

SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
        .withFunctionName("test_function");

SqlParameterSource in = new MapSqlParameterSource().addValue("id", 1);

try {
    TRPerson result = call.executeFunction(TRPerson.class, in);
} catch (DataAccessException e) {
    logger.log(Level.SEVERE, "call failed", e);
}

Then I got exception:

SEVERE: call failed
org.springframework.dao.InvalidDataAccessApiUsageException: Required input parameter 'i_out' is missing
at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:209)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1014)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1070)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:387)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:350)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(SimpleJdbcCall.java:154)

I don't understand why i_out is marked as input type. What I'm doing wrong?

Is SimpleJdbcCall suitable for my needs?

What is best the practice with stored functions and complex types results?

I would much appreciate some skeleton code to catch the pipeline.

Answer 1

I found solution, maybe it's not ideal but working well and also should work in case multiple records returned from function (piplined return). Here it is. Hope it can help you too.

String SQL = "select i_out, str_out from test_function1(:id)";
SqlParameterSource namedParameters = new MapSqlParameterSource("id", request.getIntTestVar());

List<TRPerson> result = namedTemplate.query(SQL, namedParameters, new RowMapper() {

    @Override
    public TRPerson mapRow(ResultSet rs, int i) throws SQLException {
        TRPerson result = new TRPerson();
        result.setIntVar(rs.getInt("i_out"));
        result.setStrVar(rs.getString("str_out"));
        return result;
    }
});