Pass Results Set as a Function Parameter

Help
1

Hello,

I have a query that returns a bunch of strings and I’d like to pass the strings as an array to a User Defined Function. Something along the lines of :

SELECT my_function(SELECT myString FROM myTable)

I have been banging my head against the whole for a while now and can’t seem to make it work.

2

What do you want the function to do?

Consider first putting the results of the inner SELECT into a JSON array, then pass the argument to my_function as json.

Also, a scalar UDF can take an argument of type Array but the Array can’t be returned from a query.

Consider

set @j = (select json_agg(myString) from myTable);
select my_function(@js);

or

with strings as (select json_agg(myString) as s from myTable)
select my_function(s) from strings.

See also the json_to_array and TABLE functions in the documentation.

And if your function is aggregating data from the array, consider a user-defined aggregate function: CREATE AGGREGATE · SingleStore Documentation

2 Likes