15.10 - Example: Creating and Using a UDT Input Parameter Data Type in Scalar UDF Definition - Teradata Database

This example creates a C aggregate UDF defined with a UDT input parameter and then uses it to select a UDT column from a table.

First create a new distinct type named varchar_udt:

     CREATE TYPE varchar_udt AS VARCHAR(20000) FINAL;

Now create a new function named udf_agch002002udt that uses the input parameter parameter_1 with the distinct UDT data type varchar_udt:

     CREATE FUNCTION udf_agch002002udt (
       parameter_1 varchar_udt)  
     RETURNS varchar_udt 
     CLASS AGGREGATE (20000)
     LANGUAGE C  
     NO SQL  
     EXTERNAL NAME   'CS!udf_agch002002udt!udf_agch002002udt.c'  
     PARAMETER STYLE SQL;

Suppose you have created the following table:

     CREATE TABLE aggr_data_table (
       a INTEGER, 
       b VARCHARUDT);

You then insert one row into aggr_data_table:

     INSERT INTO aggr_data_table VALUES (1, 'george');

You can now select the UDT column from aggr_date_table using the aggregate UDF udf_agch002002udt:

     SELECT udf_agch002002udt(aggr_data_table.b) 
     FROM aggr_data_table;

See SQL External Routine Programming, B035-1147 for information about how to code external functions like udf_agch002002udt.