Example: Creating an Instance Method Using a One-Dimensional ARRAY Type and an SQL Parameter Type - Teradata Vantage - Analytics Database

This example creates an instance method named update_phone with an SQL parameter type using a one-dimensional ARRAY data type that has 5 elements.

First create the ARRAY type with CHARACTER element type named phonenumbers_ary.

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

Then create the structured UDT named address_udt.

     CREATE TYPE address_udt AS (
       house_num INTEGER, 
       street VARCHAR(100), 
       phone CHAR(10))
     INSTANCE METHOD update_phone (
       a1 source_ary)
     …
     ;

Finally, create the instance method update_phone for address_udt with an SQL parameter style.

     CREATE INSTANCE METHOD update_phone (
         p1 phonenumbers_ary)
       FOR address_udt
       RETURNS INTEGER
       NO SQL
       PARAMETER STYLE SQL
       DETERMINISTIC
       LANGUAGE C
       EXTERNAL NAME 'CS!update_phone!update_phone.c!F!update_phone';
void update_phone (	
       UDT_HANDLE     *thisUdt,
       ARRAY_HANDLE   *aryval, 
       INTEGER        *result,
       int            *indicator_this,
       int            *indicator_aryval,
       int            *indicator_result,
       char           sqlstate[6],
       SQL_TEXT       extname[129],
       SQL_TEXT       specific_name[129],
       SQL_TEXT       error_message[257])
{
/* body function */
}