Example: Table Function With a UDT Parameter that Returns a Column With a UDT Data Type - Teradata Vantage - Analytics Database

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.