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;