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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 */
}