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;