17.10 - Function Parameter List - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

UDFs can define ARRAY arguments and return values, passing them by a handle.

Here is an example of how to declare a scalar function that uses an ARRAY type:

/*****  C source file name: MyArrayUDF.c  *****/
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
void MyArrayUDF( ARRAY_HANDLE   *ary_handle,
                 VARCHAR_LATIN  *result,
                 int            *indicator_ary,
                 int            *indicator_result,
                 char           sqlstate[6],
                 SQL_TEXT       extname[129],
                 SQL_TEXT       specific_name[129],
                 SQL_TEXT       error_message[257])

The following statement creates a 1-D ARRAY data type called phonenumbers_ary, which has an element type of CHAR.

/* Oracle-compatible syntax: */
CREATE TYPE phonenumbers_ary AS VARRAY(5) OF CHAR(10);
/* Teradata syntax: */
CREATE TYPE phonenumbers_ary AS CHAR(10) ARRAY[5];

If you want to use phonenumbers_ary as the input parameter type for the MyArrayUDF function, the corresponding CREATE FUNCTION statement looks like this:

CREATE FUNCTION MyArrayUDF( a1 phonenumbers_ary )
        RETURNS VARCHAR(100)
        NO SQL
        LANGUAGE C
        EXTERNAL NAME 'CS!MyArrayUDF!MyArrayUDF.c!F!MyArrayUDF';

For details on the ARRAY_HANDLE C data type, see C Data Types.