Example: Using a One-Dimensional ARRAY in a Parameter Definition - Advanced SQL Engine - Teradata Database

This example uses a one-dimensional ARRAY type with a CHARACTER element type that will be used as the data type for the single parameter a1 for the function my_array_udf.

The first step is to create an appropriate one-dimensional ARRAY type. The first CREATE TYPE request uses Oracle-compatible syntax to define the ARRAY type phonenumbers_ary.

     CREATE TYPE phonenumbers_ary      AS VARRAY(5) OF CHAR(10);

The second CREATE TYPE request uses Teradata-ANSI style syntax to define the same type.

     CREATE TYPE phonenumbers_ary      AS CHAR(10) ARRAY[5];

The following CREATE FUNCTION request creates the function my_array_udf using an SQL parameter style and uses the one-dimensional ARRAY type phonenumbers_ary as the data type of its single parameter, a1.

     CREATE FUNCTION my_array_udf(        a1 phonenumbers_ary)      RETURNS VARCHAR(100)      NO SQL      PARAMETER STYLE SQL      DETERMINISTIC      LANGUAGE C      EXTERNAL NAME 'CS!my_array_udf!my_array_udf.c'; void my_array_udf (      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]) { /* body function */ }

The following CREATE FUNCTION request creates the same function, but uses the TD_GENERAL parameter style.

     CREATE FUNCTION my_array_udf (        a1 phonenumbers_ary)      RETURNS VARCHAR(100)      NO SQL      PARAMETER STYLE TD_GENERAL      DETERMINISTIC      LANGUAGE C      EXTERNAL NAME 'CS!my_array_udf!my_array_udf.c'; void my_array_udf (      ARRAY_HANDLE *ary_handle,      VARCHAR_LATIN *result,      	char sqlstate[6]) { /* body function */ }