16.20 - Example: Creating and Using a UDT Input Parameter Data Type in Scalar UDF Definition - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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 Teradata Vantage™ SQL External Routine Programming , B035-1147 for information about how to code external functions like udf_agch002002udt.