Default Mapping Convention of Return Value Types - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

Teradata uses the same mapping conventions for return value types that it uses for parameter types.

For a Java method that implements a scalar or aggregate UDF, the data type that you use for the return value maps to the SQL data type in the RETURNS clause of the CREATE FUNCTION or REPLACE FUNCTION statement. (Table UDFs do not have return values. The columns in the result rows that they produce are returned as output parameters.)

Consider the following CREATE FUNCTION statement for a scalar UDF:

CREATE FUNCTION factorial
  (x INTEGER)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'JarUDF:UDFExample.fact';

The RETURNS clause specifies that the SQL data type of the return value is INTEGER. The signature of the fact method that implements the UDF looks like this:

public static int fact( int x ) { ... }

The default mapping convention is simple mapping, where SQL data types map to Java primitives. If no Java primitive can adequately map to an SQL type, then the default mapping convention is object mapping, where SQL data types map to Java classes.

Consider a factorial UDF that returns a DECIMAL value:

CREATE FUNCTION factorial
  (x INTEGER)
RETURNS DECIMAL(8,2)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'JarUDF:UDFExample.fact';

Because the DECIMAL type does not map adequately to a Java primitive, the DECIMAL type maps to java.math.BigDecimal. The signature of the fact method that implements the UDF looks like this:

public static BigDecimal fact( int x ) { ... }

For details on how SQL data types map to Java data types, see SQL Data Type Mapping.