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

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
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.