Return Table from User-Defined Function?

James Kane · #1 (**permalink**) 05-22-09, 10:30

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.

mirtheil · #2 (**permalink**) 05-22-09, 10:41

I haven't tried it but you might want to have the function return a string and then build the Select with the result and run the SQL using EXEC.
Something like:

Code:

declare :sSQL string;
set :sSQL = "select from test";
exec (:sSQL);

Internet Services

Web Development

Digital Marketing

Consumer Tech