17.10 - Example: Table Function With a UDT Parameter that Returns a Column With a UDT Data Type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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.