Using a SQL++ User-Defined Function to call JavaScript functions.
Introduction
Before you can call a JavaScript function, you must first create a SQL++ User-Defined Function to call it. The process to do this is explained in the Creating the SQL++ User-Defined Function section of the User-Defined Functions with JavaScript guide.
If you are unfamiliar with creating User-Defined Functions to call JavaScript, then the guide is the best place to start.
In this section, you’re going to take a closer look at concepts around SQL++ User-Defined Functions, such as variadic parameter lists.
Scopes and SQL++ User-Defined Functions
A JavaScript function can be created through the WorkBench or through the REST API.
function getBusinessDays(startDate, endDate) {
let count = 0;
const curDate = new Date(new Date(startDate).getTime());
while (curDate <= new Date(endDate)) {
const dayOfWeek = curDate.getDay();
if(dayOfWeek !== 0 && dayOfWeek !== 6)
count++;
curDate.setDate(curDate.getDate() + 1);
}
return count; (1)
}
And the corresponding SQL++ User-Defined Function can be created through the Query Workbench or by executing a SQL++ statement:
CREATE FUNCTION default:`travel-sample`.`inventory`.GetBusinessDays(startDate, endDate) (1)
LANGUAGE JAVASCRIPT as "getBusinessDays" (2)
AT "travel-sample/inventory/my-library"; (3)
1 | The new SQL++ User-Defined Function is called GetBusinessDays and takes the inventory scope inside the travel-sample bucket.
As well as providing a logical separation between JavaScript libraries, using scopes provides a means of securing access to the library: a user must have a context that matches the scope of the library in order to access it. |
2 | This function will reference the getBusinessDays JavaScript function … |
3 | … in a library called my-library which is set to the inventory scope within the travel-sample bucket. |
Global Library Path
Of course, you can define the library at the cluster level, where it will be accessible to anyone who has access rights to the cluster. Functions in the global library are accessible across the cluster.
CREATE FUNCTION GetBusinessDays(startDate, endDate)
LANGUAGE JAVASCRIPT as "getBusinessDays"
AT "my-library"; (1)
1 | There is no prefix path before my-library which means the library is a globally accessible library defined at the cluster level. |
Relative Library Path
You can also use relative paths for the library location:
CREATE FUNCTION GetBusinessDays(startDate, endDate)
LANGUAGE JAVASCRIPT as "getBusinessDays"
AT "./my-library"; (1)
1 | In this case, the User-Defined Function will be created for the JavaScript function under the current query context. |
Calling the Function
Once the SQL++ User-Defined Function is defined, it can be called as if it were a built-in SQL++ function:
SELECT GetBusinessDays('02/14/2022', '4/16/2022');
which will return the following result:
[
{
"$1": 45
}
]
You can also use the EXECUTE FUNCTION
statement to execute the function:
EXECUTE FUNCTION GetBusinessDays("02/14/2022", "04/16/2022");
or as part of a complex statement:
SELECT CASE
WHEN GetBusinessDays('02/14/2022', '4/16/2022') > 44 THEN "true"
ELSE "false"
END
AS response; (1)
Variadic Parameters
You can define a SQL++ User-Defined Function with a variadic parameter, which means that the parameter will accept a list of values which it will pass to the JavaScript function it references.
You can create the GetBusinessDays
function using a variadic parameter rather than the startDate
and endDate
parameters:
CREATE FUNCTION GetBusinessDays(...)
LANGUAGE JAVASCRIPT as "getBusinessDays"
AT "my-library";
Note that the statement used three dots (…) rather than a list of parameter list. This indicates a variable length parameter list. The underlying JavaScript function will reference the parameter list as named variables:
function getBusinessDays(startDate, endDate) {
// Do calculations
}
You can also use a variable length parameter list in the JavaScript function itself:
function sumListOfNumbers(... args) { (1)
var sum = 0;
args.forEach(value => sum = sum + value); (2)
return sum;
}
1 | JavaScript uses three dots (…) followed by a parameter name to denote a parameter that is an array of values. |
2 | Scans through the variadic parameter list, summing all the numbers it contains. |
A SQL++ User-Defined Function can now be created that takes a variable length list of numbers as an argument:
CREATE FUNCTION SumListOfNumbers(...)
LANGUAGE JAVASCRIPT as "sumListOfNumbers"
AT "my-library";
which can then be called with a variable length list of numbers as a parameter
EXECUTE FUNCTION SumListOfNumbers(1, 2, 4, 8, 16, 32, 64);
[
127
]