16.20 - Example: Table Function With Dynamic Result Row Specification - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Here is an example of how to declare a table function with dynamic result row specification:

/*****  C source file name: store_data.c  *****/
   
#define  SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void get_store_data( INTEGER  *filetoread,    /* input argument  */
                     void     *out1,          /* output argument */
                     void     *out2,          /* output argument */
                     void     *out3,          /* output argument */
                     void     *out4,          /* output argument */
                     int      *filetoreadIsNull,
                     int      *out1IsNull,
                     int      *out2IsNull,
                     int      *out3IsNull;
                     int      *out4IsNull;
                     char     sqlstate[6],
                     SQL_TEXT extname[129],
                     SQL_TEXT specific_name[129],
                     SQL_TEXT error_message[257] )
{
     ...
}

The corresponding CREATE FUNCTION statement that installs the table function on the server looks like this:

CREATE FUNCTION getStoreData
  (FileToRead INTEGER)
RETURNS TABLE VARYING COLUMNS (4)
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!getstoredata!udfsrc/store_data.c!F!get_store_data'
PARAMETER STYLE SQL;

Here is an example of an INSERT … SELECT statement that invokes the table function in the FROM clause:

INSERT INTO Sales_Table
SELECT *
FROM TABLE (getStoreData(9005)
   RETURNS (Store INTEGER, Item INTEGER, Quantity INTEGER)) AS tf;