Spring spring-jdbc don't set out parameter name from metadata for Postgresql function after issue#25399

Affects: 5.2.8.RELEASE

When using PostgreSQL driver 42.2.11 and higher and spring-jdbc version 5.2.8.RELEASE and calling Postgresql function then CallMetaDataContext.actualFunctionReturnName from function metadata is not set. Related to issue # 25399 there was a split in GenericCallMetaDataProvider:

function?
databaseMetaData.getFunctionColumns (metaDataCatalogName, metaDataSchemaName, metaDataProcedureName, null):
databaseMetaData.getProcedureColumns (metaDataCatalogName, metaDataSchemaName, metaDataProcedureName, null)

And for functions it returns columnType = 4, previously it returned columnType = 5.

Because of this, the condition is not met in CallMetaDataContext.reconcileParameters:

if (meta.isReturnParameter()) {
        // DatabaseMetaData.procedureColumnReturn or possibly procedureColumnResult
        if (!isFunction() && !isReturnValueRequired() && paramName != null &&
                provider.byPassReturnParameter(paramName)) {
            if (logger.isDebugEnabled()) {
                logger.debug("Bypassing meta-data return parameter for '" + paramName + "'");
            }
        }
        else {
            String returnNameToUse =
                    (StringUtils.hasLength(paramNameToUse) ? paramNameToUse : getFunctionReturnName());
            workParams.add(provider.createDefaultOutParameter(returnNameToUse, meta));
            if (isFunction()) {
                setFunctionReturnName(returnNameToUse);
                outParamNames.add(returnNameToUse);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("Added meta-data return parameter for '" + returnNameToUse + "'");
            }
        }
    }

and setFunctionReturnName is not called. It is not executed because isReturnParameter looks like:

public boolean isReturnParameter() {
        return (this.parameterType == DatabaseMetaData.procedureColumnReturn ||
                this.parameterType == DatabaseMetaData.procedureColumnResult);
    }

Only the type of parameters for the procedure are checked.

Because of this, the output parameter for the function is passed in the metadata as returnValue, setFunctionReturnName is not called, and spring uses the default name return, and does not find such a parameter in the results.

Comment From: benht-nps

I've found the same issue and can add a little more detail.

Looks like GenericCallMetaDataProvider.processProcedureColumns first identifies it as a function rather than a procedure.

PgDatabaseMetaData.getFunctionColumns (ie the Postgresql driver) sets the return type to java.sql.DatabaseMetaData.functionReturn now (which has magic number 4 whereas in the previous driver it was set to DatabaseMetaData.procedureColumnReturn with magic number 5).

CallMetaDataContext.reconcileParameters calls CallParameterMetaData.isReturnParameter which still only looks for the procedure types, not function types.

It can't simply look for both because the numbers would conflict. It looks like it needs an extra parameter to tell it if it's a procedure or a function.

Comment From: benht-nps

Nice one, thank you 👍