Example: Table Function With a UDT Parameter that Returns a Column With a UDT Data Type - 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 creates a table UDF with a UDT parameter that returns a distinct UDT column named udtc4.

First create a new distinct data type named TABLEINT:

     CREATE TYPE TABLEINT AS INTEGER FINAL;

Now create a new table function named fnc_tbf001udt that declares the input parameter p2 with a data type of TABLEINT:

     CREATE  FUNCTION fnc_tbf001udt(
       p1 INTEGER, 
       p2 TABLEINT)
     RETURNS TABLE (c1    INTEGER,  
                    c2    INTEGER,  
                    c3    VARCHAR(3),  
                    udtc4 TABLEINT)
     LANGUAGE C  
     NO SQL  
     PARAMETER STYLE SQL  
     EXTERNAL NAME 'CS!fnc_tbf001udt!fnc_tbf001udt.c'; 

Now use the table function in a SELECT request to return results in the form of a table:

     SELECT * 
     FROM TABLE(fnc_tbf001udt(1, 1)) AS t1 
     WHERE t1.c2 IN (0,1);

See Teradata Vantage™ - SQL External Routine Programming , B035-1147 for information about how to code external functions similar to fnc_tbf001udt.