You are viewing the documentation for a prerelease version.

User-Defined Functions

    +

    In N1QL for Analytics, user-defined functions enable you to name and reuse complex or repetitive expressions, including subqueries, in order to simplify your queries.

    Each user-defined function belongs to an Analytics scope. Within an Analytics scope, a function is uniquely identified by its function signature: that is, its name and the number of parameters it takes. You can create more than one function with the same name in the same Analytics scope, as long as each function has a different number of parameters.

    You can query the Function Analytics collection within the Metadata Analytics scope to get metadata about all existing user-defined functions. Note that FUNCTION is a reserved keyword, so you need to delimit the name of the Function collection with backticks (`).

    You can’t create a function in the Metadata scope.

    Creating a Function

    CreateFunction
    "CREATE" ( "OR" "REPLACE" )? "ANALYTICS" "FUNCTION" QualifiedName "(" FunctionParameters? ")" ("IF" "NOT" "EXISTS")? "{" Query "}"

    The CREATE ANALYTICS FUNCTION command enables you to create a user-defined function.

    Function Name

    QualifiedName
    (ScopeName ".")? Identifier
    ScopeName
    (Identifier ".")? Identifier

    The QualifiedName specifies the name of the function to create. It consists of an optional scope name, followed by an identifier which represents the local name of the function.

    The optional ScopeName specifies the Analytics scope where the function is located. It may consist of one or two identifiers, separated by a dot.

    If you don’t specify an Analytics scope, then the scope containing the function is resolved according to the following rules:

    1. If there is a preceding USE statement, the function is located in the Analytics scope specified by the USE statement.

    2. Otherwise, if the query_context parameter is set, the function is located in the Analytics scope specified by the query context.

    3. Otherwise, the function is located in the Default Analytics scope.

    Parameter List

    FunctionParameters
    Identifier ( "," Identifier )* | "…​"

    The parameter list specifies parameters for the function. It is delimited by parentheses (). You can specify named parameters for the function using a comma-separated list of identifiers.

    If you specify named parameters for the function, then you must call the function with exactly the same number of arguments at execution time. If you specify no parameters, then you must call the function with no arguments.

    To create a variadic function, that is, a function which you can call with any number of arguments or none, specify ... as the only parameter.

    Function Body

    Query
    Expr | Selection

    The function body defines the function. It is delimited by braces {}. It may contain any valid expression or subquery.

    If you specified named parameters for the function, you can use these in the function body to represent arguments which are passed to the function at execution time. If you specified that the function is variadic, any arguments passed to the function at execution time are held in an array named args.

    The function body may refer to other Analytics collections, or to other functions in the same or other Analytics scopes. If an Analytics scope name was explicitly specified when creating the function, then that scope is the default scope for function calls or collection references within the function body.

    Checking for an Existing Function and Replacing a Function

    The optional IF NOT EXISTS keywords enable you to check whether a user-defined function exists before creating it, and the optional OR REPLACE keywords enable you to redefine the function.

    If a function with the same signature already exists within the specified Analytics scope, then:

    • If the OR REPLACE keywords are present, the existing function is replaced.

    • If the OR REPLACE keywords are not present, then:

      • If the IF NOT EXISTS keywords are present, the statement does nothing and completes without error.

      • If the IF NOT EXISTS keywords are not present, an error is generated.

    Examples

    For simplicity, none of these examples implement any data validation or error checking.

    Example 1: Variadic function

    This statement creates a function called to_celsius, which converts Fahrenheit to Celsius. The function is variadic.

    For purposes of illustration, this expression converts just the first argument supplied at execution time, which is stored in the first member in the args array. A more realistic variadic function would make use of all the supplied arguments.

    CREATE ANALYTICS FUNCTION to_celsius(...) { (args[0] - 32) * 5/9 };
    Test
    to_celsius(100);
    Result
    [
      37.77777777777778
    ]
    Example 2: Variadic function with extra arguments

    This statement creates a function called to_fahrenheit, which converts Celsius to Fahrenheit. The function is variadic.

    For purposes of illustration, this expression converts just the first argument supplied at execution time, which is stored in the first member in the args array. A more realistic variadic function would make use of all the supplied arguments.

    CREATE ANALYTICS FUNCTION to_fahrenheit(...) { (args[0] * 9/5) + 32 };
    Test
    to_fahrenheit(100, "ignore this");
    Result
    [
      212
    ]

    As the function is variadic, you can use any number of arguments when you call the function. Arguments which are not used by the function expression are ignored.

    Example 3: Function with fixed parameters

    The following statement creates a function called rstr, which returns the specified number of characters from the right of a string. The expression expects two named arguments: vString, which is the string to work with, and vLen, which is the number of characters to return.

    CREATE ANALYTICS FUNCTION rstr(vString, vLen) { substr(vString, length(vString) - vLen, vLen) };
    Test
    rstr("Couchbase", 4);
    Result
    [
      "base"
    ]
    Example 4: Function with fixed parameters and extra arguments

    The following statement creates a function called lstr, which returns the specified number of characters from the left of a string. The expression expects two named arguments: vString, which is the string to work with, and vLen, which is the number of characters to return.

    CREATE ANALYTICS FUNCTION lstr(vString, vLen) { substr(vString, 0, vLen) };
    Test
    lstr("Couchbase", 5, "ignore this");

    This function returns an error. As the arguments were specified by the function definition, you must use the same number of arguments when you call the function. If you supply the wrong number of arguments, an error is generated.

    Example 5: Function with no parameters

    This statement creates a function which returns the mathematical constant φ. The function takes no arguments.

    CREATE ANALYTICS FUNCTION phi() { 2 * sin(radians(54)); };
    Test
    phi();
    Result
    [
      1.618033988749895
    ]
    Example 6: Replace a function

    The following statement redefines the function so that it calculates φ using a different method.

    CREATE OR REPLACE ANALYTICS FUNCTION phi() { (1 + sqrt(5)) / 2 };
    Test
    phi();
    Result
    [
      1.618033988749895
    ]

    Calling a Function

    OrdinaryFunctionCall

    You can invoke a user-defined function in the same way as any other ordinary function. You may optionally prefix the name of the function with the ScopeName of the Analytics scope containing the function.

    If you don’t specify an Analytics scope when calling a function, then the scope containing the function is resolved according to the following rules:

    1. If the function call is enclosed within the function body of another user-defined function, the scope is that of the enclosing function.

    2. Otherwise, if there is a preceding USE statement, the Analytics scope is specified by the USE statement.

    3. Otherwise, if the query_context parameter is set, the Analytics scope is specified by the query context.

    4. Otherwise, use the Default Analytics scope.

    Having determined the scope, Analytics tries to find a user-defined function with the same function signature within that scope. If a user-defined function cannot be found, Analytics tries to find a built-in function with the same function name. If a built-in function cannot be found, the function call fails.

    Examples

    Example 7: Function with subquery

    The following statement creates a function called nameSearch, which selects the customer name from all documents with the specified ID in the customers collection.

    CREATE ANALYTICS FUNCTION nameSearch(customerId) {
      (SELECT VALUE c.name
      FROM customers AS c
      WHERE c.custid = customerId)[0]
    }
    Test
    SELECT VALUE nameSearch("C25");
    Result
    [
      { "custid": "C25", "name": "M. Sinclair" }
    ]
    Example 8: Call a user-defined function

    The following query uses the nameSearch function as a projection expression in a SELECT query. Compare this with example Q3.29 in the section on Subqueries.

    SELECT o.orderno, o.custid,
          nameSearch(o.custid) AS name
    FROM orders AS o, o.items AS i
    WHERE i.itemno = 120;
    Result
    [
        {
            "orderno": 1003,
            "custid": "C31",
            "name": "B. Pruitt"
        },
        {
            "orderno": 1006,
            "custid": "C41",
            "name": "R. Dodge"
        }
    ]

    Dropping a Function

    DropFunction
    "DROP" "ANALYTICS" "FUNCTION" QualifiedName "(" FunctionParameters? ")" ( "IF" "EXISTS" )?

    The DROP ANALYTICS FUNCTION statement enables you to delete a user-defined function.

    You cannot delete a user-defined function if there are any other user-defined functions which call this function, in this Analytics scope or any other Analytics scope.

    Function Name

    QualifiedName
    (ScopeName ".")? Identifier
    ScopeName
    (Identifier ".")? Identifier

    The QualifiedName specifies the name of the function to delete. It consists of an optional scope name, followed by an identifier which represents the local name of the function.

    The optional ScopeName specifies the Analytics scope where the function is located. It may consist of one or two identifiers, separated by a dot.

    If you don’t specify an Analytics scope, then the scope containing the function is resolved according to the same rules that are used when creating a user-defined function.

    Parameter List

    FunctionParameters
    Identifier ( "," Identifier )* | "…​"

    When you drop a function, you must specify the same number of parameters that you specified when you created the function, or specify ... if you are dropping a variadic function.

    Checking for an Existing Function

    The optional IF EXISTS keywords enable you to check whether the specified function or scope exists before dropping it. If these keywords are present, and the function scope or function name are unknown, the statement does nothing and completes without error.

    If these keywords are not present, and the function scope or function name are unknown, an error is generated.

    Examples

    Example 9: Drop a Function

    This statement drops the function named nameSearch, if it exists.

    DROP ANALYTICS FUNCTION nameSearch(customerId) IF EXISTS;

    You can run the following query to check that the function is no longer available.

    SELECT * FROM Metadata.`Function`;