Example: Table Function with Fixed Result Row Specification - Analytics Database - Teradata Vantage

Here is an example of how to declare a table function:

/*****  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  */
                     INTEGER  *storeno,       /* output argument */
                     INTEGER  *itemno,        /* output argument */
                     INTEGER  quantsold,      /* output argument */
                     int      *filetoreadIsNull,
                     int      *storenoIsNull,
                     int      *itemnoIsNull,
                     int      *quantsoldIsNull;
                     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
   (StoreNo   INTEGER
   ,ItemNo    INTEGER
   ,QuantSold INTEGER)
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)) AS tf;