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

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
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
        PARAMETER STYLE SQL
        DETERMINISTIC
        LANGUAGE C
        EXTERNAL NAME 'CS!MyArrayUDF!MyArrayUDF.c!F!MyArrayUDF';

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