Syntax for Table Function - Advanced SQL Engine - Teradata Database

SQL External Routine Programming
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™
void  function_name  (  type_1        *input_parameter_1,
                      ...,
                     type_n        *input_parameter_n,
                     result_type_1  *result_1,
                      ...,
                     result_type_r  *result_r,
                     int           *indicator_parameter_1,
                      ...,
                     int           *indicator_parameter_n,
                     int           *indicator_result_1,
                      ...,
                     int           *indicator_result_r,
                     char           sqlstate[6],
                     SQL_TEXT       function_name[m],
                     SQL_TEXT       specific_function_name[l],
                     SQL_TEXT       error_message[p] )
{
     ...
}

where:

Parameter … Specifies … Input/Output
type_n * input_parameter_n the input arguments, where n is the number of parameters in the CREATE FUNCTION or REPLACE FUNCTION definition. If n = 0, no input parameters appear. The type is one of the C types in sqltypes_td.h that corresponds to the SQL data type of the input argument. In
result_type_r * result_r the row result arguments, where r is determined by the corresponding CREATE FUNCTION or REPLACE FUNCTION definition.

If the table function is defined with fixed result row specification, then...

  • r is the number of columns in the column list in the RETURNS TABLE clause in the corresponding CREATE FUNCTION definition.
  • Each result type is a C type in sqltypes_td.h that matches the SQL data type of the corresponding column in the CREATE FUNCTION statement.
  • Each result_r pointer points to a data area that is big enough for a value of the corresponding column type in the RETURNS TABLE clause in the CREATE FUNCTION statement.
If the table function is defined with dynamic result row specification, then...
  • r is the maximum number of columns in the RETURNS TABLE VARYING COLUMNS clause in the corresponding CREATE FUNCTION definition.
  • Each result_r pointer must be declared as a void pointer because the actual data types of the result row arguments are unknown until function invocation.
  • During execution, the table function can call the FNC_TblGetColDef library function to get the actual result types.
  • Each result_r pointer points to a data area that is big enough for a value of the corresponding column type in the SELECT statement that invokes the table function.

The function must return at least one result argument.

 Out
int * indicator_parameter_n the indicator arguments (for functions where n > 0) corresponding to the input arguments, in the same order.

If the value of the indicator argument is...

  • -1, then the corresponding input argument is null.
  • 0, then the corresponding input argument is a value.
 In
int * indicator_result_r whether the function needs to return the corresponding result arguments.

If the value of the indicator argument is...

  • -1, then the corresponding result argument does not need to be returned by the function.

    For a table function that is defined with dynamic result row specification, a value of -1 means that the corresponding result argument was omitted in the SELECT statement that invoked the table function.

  • 0, then the corresponding result argument must be returned by the function.

If the function returns NULL for a result argument, the function must set the corresponding indicator argument to -1.

 In/Out
char sqlstate[6] the success, exception, or warning return. This is a pointer to a six-character C string, where the first five characters are ASCII and the last character is a C null character. The function can set sqlstate to an SQLSTATE exception or warning condition if the function detects an error.

The string is initialized to '00000', which corresponds to a success condition.

For more information on SQLSTATE values, see Returning SQLSTATE Values.

Out
SQL_TEXT function_name[m] the function name. This is a pointer to a C string. This is the same name as the function name specified by CREATE FUNCTION function_name.

The function can use this name to build error messages.

The ANSI SQL standard defines the maximum value for m as 128. Teradata Database allows a maximum of 30 characters for function names.

 In
SQL_TEXT specific_function_name[l] the specific name of the external function being invoked, when more than one function has the same name. This is a pointer to a C string. This is the same name as the specific name specified by the SPECIFIC clause of the CREATE FUNCTION statement.

If the CREATE FUNCTION statement omits the SPECIFIC clause, this name is the same as the function name specified by CREATE FUNCTION function_name.

The function can use this name to build error messages.

The ANSI SQL standard defines the maximum value for l as 128. Teradata Database allows a maximum of 30 characters for function names.

 In
SQL_TEXT error_message[p] the error message text. This is a pointer to a C string where the maximum value for p is 256. Out