Unanswered: Return Table from User-Defined Function?
After some quick experimentation, I'm looking for some feedback from those more experience with the Pervasive SQL engine. Basically, I am attempting to implement a UDF that takes a CSV argument and returns the elements in a table. The intent is to call the UDF from a construct in a stored procedure similar to this:
WHERE id IN (SELECT id FROM udf_Split('''6'', ''7'', ''42'''))
The road block is that it appears you cannot return a table from a UDF in Pervasive as I am getting a syntax error shortly after the BEGIN statement. In looking at CREATE FUNCTION's help entry, it appears this would be because TABLE cannot be used with the RETURNS. Is this indeed not supported in Pervasive?
If not, does anyone have a good solution for this limitation? I'm thinking a temp table created from the main app is the only way to go otherwise.