Scalar JavaScript UDFs

This topic covers Scalar JavaScript UDFs (user-defined function).

In this Topic:

Introduction

A scalar JavaScript UDF returns one output row for each input row. The output row must contain only one column/value.

A basic example is in Introduction to JavaScript UDFs. Additional examples are below.

Examples

This section contains examples of scalar JavaScript UDFs.

This example shows that a JavaScript UDF can call itself (i.e. it can use recursion):

Create a recursive UDF:

CREATE OR REPLACE FUNCTION RECURSION_TEST (STR VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS $$
  return (STR.length <= 1 ? STR : STR.substring(0,1) + '_' + RECURSION_TEST(STR.substring(1)));
  $$
  ;

Call the recursive UDF:

SELECT RECURSION_TEST('ABC');
+-----------------------+
| RECURSION_TEST('ABC') |
|-----------------------|
| A_B_C                 |
+-----------------------+

This example shows a JavaScript UDF that throws a custom exception:

Create the function:

CREATE FUNCTION validate_ID(ID FLOAT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    try {
        if (ID < 0) {
            throw "ID cannot be negative!";
        } else {
            return "ID validated.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;

Create a table with valid and invalid values:

CREATE TABLE employees (ID INTEGER);
INSERT INTO employees (ID) VALUES 
    (1),
    (-1);

Call the function:

SELECT ID, validate_ID(ID) FROM employees ORDER BY ID;
+----+-------------------------------+
| ID | VALIDATE_ID(ID)               |
|----+-------------------------------|
| -1 | Error: ID cannot be negative! |
|  1 | ID validated.                 |
+----+-------------------------------+

Troubleshooting

Error Message: Variable is not defined

Cause

In some cases, this error message might be caused by an ampersand (&) inside a CREATE FUNCTION command because the ampersand is the variable substitution character. For example:

create function mask_bits(...)
    ...
    as
    $$
    var masked = (x & y);
    ...
    $$;

The error will occur at the time that the function is created, not at the time that the function is called.

Solution

If you do not intend to use variable substitution, you can explicitly disable it. For example, in SnowSQL, you can disable variable substitution by executing the following command:

!set variable_substitution=false;

For more information about variable substitution, see Using Variables.