How to apply a to a stored function in jOOQ?

Question

A common question among jOOQ users is how a <forcedType> can be applied to a stored function return type in the code generator. The manual specifies that <includeExpression> matches qualified or unqualified identifiers, so given this HSQLDB function:

CREATE FUNCTION stored_functions.f_1 (p_i int)
RETURNS int
BEGIN ATOMIC
  RETURN p_i;
END

The parameter of the function can be forced to String using:

<forcedType>
  <userType>java.lang.String</userType>
  <converter>
    org.jooq.Converter.ofNullable(Integer.class, String.class, Object::toString, Integer::valueOf)
  </converter>
  <includeExpression>(?i:f_1\.p_i)</includeExpression>
</forcedType>

This produces the following Parameter specification:

/**
 * The parameter <code>STORED_FUNCTIONS.F_1.P_I</code>.
 */
public static final Parameter<String> P_I = Internal.createParameter(
  "P_I", org.jooq.impl.SQLDataType.INTEGER, false, false, 
  org.jooq.Converter.ofNullable(Integer.class, String.class, Object::toString, Integer::valueOf)
);

How to do the same for the return value?

Answer 1

There is a synthetic parameter name that can be used. It's called return_value, which is also the name of the generated Parameter literal.

The following specification will apply the same converter to both the P_I parameter and RETURN_VALUE:

<forcedType>
  <userType>java.lang.String</userType>
  <converter>
    org.jooq.Converter.ofNullable(Integer.class, String.class, Object::toString, Integer::valueOf)
  </converter>
  <includeExpression>(?i:f_1\.p_i|return_value)</includeExpression>
</forcedType>

This produces the following Parameter specifications:

/**
 * The parameter <code>STORED_FUNCTIONS.F_1.RETURN_VALUE</code>.
 */
public static final Parameter<String> RETURN_VALUE = Internal.createParameter(
  "RETURN_VALUE", org.jooq.impl.SQLDataType.INTEGER, false, false, 
  org.jooq.Converter.ofNullable(Integer.class, String.class, Object::toString, Integer::valueOf)
);

/**
 * The parameter <code>STORED_FUNCTIONS.F_1.P_I</code>.
 */
public static final Parameter<String> P_I = Internal.createParameter(
  "P_I", org.jooq.impl.SQLDataType.INTEGER, false, false, 
  org.jooq.Converter.ofNullable(Integer.class, String.class, Object::toString, Integer::valueOf)
);