Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

CREATE FUNCTION

  • Capella Operational
  • reference
    +
    The CREATE FUNCTION statement enables you to create a user-defined function.

    Purpose

    There are two types of user-defined function in SQL++ for Query:

    • Inline functions are defined using SQL++ expressions. They enable you to name and reuse complex or repetitive expressions, including subqueries, in order to simplify your queries.

    • External functions are defined using an external language. They enable you to create functions that may be difficult or impossible to define using built-in SQL++ expressions. The only supported language is JavaScript.

    External functions in SQL++ support most of the language constructs available in ECMAScript. For more information about the restrictions and extensions that come with the Couchbase implementation, see javascript-udfs:javascript-functions-with-couchbase.adoc.

    Global Functions and Scoped Functions

    You can create user-defined functions at two different levels of the SQL++ logical hierarchy.

    • A global function is created within a namespace, at the same level as the buckets within the namespace. When you call a global function, any partial keyspace references within the function definition are resolved against the function’s namespace, regardless of the current query context.

      For example, when you call a global function default:global() which contains the keyspace reference `travel-sample`, the keyspace reference is always resolved within the context of the function to the default:`travel-sample` bucket.

    • A scoped function is created within a scope, at the same level as the collections within the scope. When you call a scoped function, any partial keyspace references within the function definition are resolved against the function’s scope, regardless of the current query context.

      For example, when you call a scoped function default:`travel-sample`.inventory.scope() which contains the keyspace reference route, the keyspace reference is always resolved within the context of the function to default:`travel-sample`.inventory.route.

    When you create a user-defined function, the current query context determines whether it is created as a global function or a scoped function. If you want to create a user-defined function outside of the current query context, you must include the full path to the function when you specify the function name.

    Similarly, when you call a user-defined function, the current query context determines the path to the function. If you want to call a user-defined function outside of the current query context, you must include the full path to the function when you specify the function name.

    Finally, it is important to note that a global function is not the same as a scoped function stored in the default scope in a bucket.

    SQL++ Managed User-Defined Functions

    Couchbase Server 7.6

    In clusters using Couchbase Server 7.6 and later, you can create the code for an external function and the corresponding SQL++ user-defined function in a single operation. This means that you don’t have to specify an external library and create the code for the external function, before creating the SQL++ user-defined function.

    With a SQL++ managed user-defined function, the external function code is stored inline, along with the SQL++ user-defined function. You cannot share this external function code with other user-defined functions, or access it from any external libraries.

    Prerequisites

    To manage …​ You must have …​

    Global inline functions

    Manage Global Functions role.

    Scoped inline functions

    Manage Scope Functions role, with permissions on the specified bucket and scope.

    Global external functions

    Manage Global External Functions role.

    Scoped external functions

    Manage Scope External Functions role, with permissions on the specified bucket and scope.

    Users with the Manage Scope External Functions role also have read-only access to any global external library.

    To execute …​ You must have …​

    Global inline functions

    Execute Global Functions role.

    Scoped inline functions

    Execute Scope Functions role, with permissions on the specified bucket and scope.

    Global external functions

    Execute Global External Functions role.

    Scoped external functions

    Execute Scope External Functions role, with permissions on the specified bucket and scope.

    For more details about user roles, see Authorization.

    Syntax

    The CREATE FUNCTION statement takes a different syntax depending on the type of function you are creating. Refer to Inline Functions or External Functions below.

    create-function ::= create-function-inline | create-function-external
    Syntax diagram: refer to source code listing

    Inline Functions

    There are two alternative syntaxes for defining an inline function: a syntax with braces {} and a syntax using the LANGUAGE keyword. The two syntaxes are synonymous.

    create-function-inline ::= 'CREATE' ( 'OR' 'REPLACE' )? 'FUNCTION' function '(' params? ')'
                           ( 'IF' 'NOT' 'EXISTS' )?
                           ( '{' body '}' | 'LANGUAGE' 'INLINE' 'AS' body )
    Syntax diagram: refer to source code listing
    function

    Function Name

    params

    Function Parameters

    body

    Function Body

    OR REPLACE / IF NOT EXISTS

    The optional OR REPLACE clause enables you to redefine a user-defined function if it already exists, whereas the optional IF NOT EXISTS clause enables the statement to complete successfully without replacing the function.

    When a function with the same name already exists within the same context: [1]

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

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

    • If neither of these two clauses is present, an error is generated.

    These clauses are exclusive. If the statement contains both the OR REPLACE clause and the IF NOT EXISTS clause, an error is generated.

    Function Name

    function ::= ( namespace ':' ( bucket '.' scope '.' )? )? identifier
    Syntax diagram: refer to source code listing

    The function name specifies the name of the function to create. It is recommended to use an unqualified identifier for the function name, such as func1 or `func-1`. In this case, the function is created as a global function or a scoped function, depending on the current query context.

    To create a global function in a particular namespace, the function name must be a qualified identifier with a namespace, such as default:func1. Similarly, to create a scoped function in a particular scope, the function name must be a qualified identifier with the full path to a scope, such as default:`travel-sample`.inventory.func1.

    If the function name is an unqualified identifier, it may not be the same as a reserved keyword. A function name with a specified namespace or scope may have the same name as a reserved keyword.

    Function Parameters

    params ::= identifier ( "," identifier )* | "..."
    Syntax diagram: refer to source code listing

    [Optional] The function parameter list specifies parameters for the function. 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

    The function body defines the function. You can use any valid SQL++ expression. If you specified named parameters for the function, you can use these in the expression to represent arguments 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.

    • If the expression contains a parameter that has the same name as a field in the document, it will always refer to the parameter. To distinguish between the field and the parameter, prefix the field with the keyspace name, for example landmark.activity. To avoid this ambiguity, you should use unique parameter names that do not clash with document field names, such as vActivity.

    • Functions may return only one value, of any valid SQL++ type. For inline functions, the result and type of the function are the result and type of the expression. If you need to return multiple values, construct an array.

    External Functions

    create-function-external ::= 'CREATE' ( 'OR' 'REPLACE' )? 'FUNCTION' function '(' params? ')'
                             ( 'IF' 'NOT' 'EXISTS' )?
                             'LANGUAGE' 'JAVASCRIPT' 'AS' ( obj 'AT' library | javascript )
    Syntax diagram: refer to source code listing
    function

    Function Name

    params

    Function Parameters

    obj

    External Object

    library

    External Library

    javascript

    Function Body

    OR REPLACE / IF NOT EXISTS

    The optional OR REPLACE clause enables you to redefine a user-defined function if it already exists, whereas the optional IF NOT EXISTS clause enables the statement to complete successfully without replacing the function.

    When a function with the same name already exists within the same context: [1]

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

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

    • If neither of these two clauses is present, an error is generated.

    These clauses are exclusive. If the statement contains both the OR REPLACE clause and the IF NOT EXISTS clause, an error is generated.

    Function Name

    function ::= ( namespace ':' ( bucket '.' scope '.' )? )? identifier
    Syntax diagram: refer to source code listing

    The function name specifies the name of the function to create. It is recommended to use an unqualified identifier for the function name, such as func1 or `func-1`. In this case, the function is created as a global function or a scoped function, depending on the current query context.

    To create a global function in a particular namespace, the function name must be a qualified identifier with a namespace, such as default:func1. Similarly, to create a scoped function in a particular scope, the function name must be a qualified identifier with the full path to a scope, such as default:`travel-sample`.inventory.func1.

    If the function name is an unqualified identifier, it may not be the same as a reserved keyword. A function name with a specified namespace or scope may have the same name as a reserved keyword.

    Function Parameters

    params ::= identifier ( "," identifier )* | "..."
    Syntax diagram: refer to source code listing

    [Optional] The function parameter list specifies parameters for the function. 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.

    External Object

    [Optional] Use this parameter where the function code is stored in an external library.

    Note that Capella does not currently support a way to create or manage an external library.

    External Library

    [Optional] Use this parameter where the function code is stored in an external library.

    Note that Capella does not currently support a way to create or manage an external library.

    Function Body

    [Optional] Use this parameter to create a SQL++ managed user-defined function.

    The external JavaScript function code. This must contain a function with the same name and the same number of parameters as the SQL++ user-defined function. This parameter is a string and must be wrapped in quotes.

    The JavaScript code can contain multiple function definitions, but these functions can only be referenced within the JavaScript code for this SQL++ user-defined function, and cannot be shared.

    Examples

    For simplicity, none of these examples implement any data validation or error checking. If necessary, you can use conditional operators to check the parameters of a user-defined function, and the ABORT() function to generate an error if something is wrong.

    Example 1. Inline function with the LANGUAGE syntax

    This statement creates a function called 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 FUNCTION celsius(...) LANGUAGE INLINE AS (args[0] - 32) * 5/9;
    Test
    EXECUTE FUNCTION celsius(100);
    Result
    [
  37.77777777777778
]
    Example 2. Inline function with the braces syntax

    This statement creates a function called 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 FUNCTION fahrenheit(...) { (args[0] * 9/5) + 32 };
    Test
    EXECUTE FUNCTION 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. Inline function with named parameters

    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 FUNCTION lstr(vString, vLen) LANGUAGE INLINE AS SUBSTR(vString, 0, vLen);
    Test
    EXECUTE FUNCTION lstr("Couchbase", 5, "ignore this");
    Result
    [
  {
    "code": 10104,
    "msg": "Incorrect number of arguments supplied to function lstr - cause: lstr"
  }
]

    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 4. Inline function with named 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 FUNCTION rstr(vString, vLen) { SUBSTR(vString, LENGTH(vString) - vLen, vLen) };
    Test
    EXECUTE FUNCTION rstr("Couchbase", 4);
    Result
    [
  "base"
]
    Example 5. Inline function with subquery

    The following statement creates a function called locations, which selects name and address information from all documents with the specified activity in the landmark keyspace.

    CREATE FUNCTION locations(vActivity) { (
  SELECT id, name, address, city
  FROM landmark
  WHERE activity = vActivity) };
    Test
    EXECUTE FUNCTION locations("see");
    Result
    [
  [
    {
      "address": "Prince Arthur Road, ME4 4UG",
      "city": "Gillingham",
      "id": 10019,
      "name": "Royal Engineers Museum"
    },
    {
      "address": "84 rue Claude Monet",
      "city": "Giverny",
      "id": 10061,
      "name": "Monet's House"
    },
...
    Example 6. Replace a function

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

    CREATE FUNCTION phi() { 2 * SIN(RADIANS(54)) };
    Test
    EXECUTE FUNCTION phi();
    Result
    [
  1.618033988749895
]

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

    Replace
    CREATE OR REPLACE FUNCTION phi() { (1 + SQRT(5)) / 2 };
    Test
    EXECUTE FUNCTION phi();
    Result
    [
  1.618033988749895
]
    Example 7. SQL++ managed user-defined function

    The following statement creates external JavaScript function code and the corresponding SQL++ user-defined function in one operation.

    CREATE FUNCTION add100(num) LANGUAGE JAVASCRIPT AS
"function add100(param1) {return param1+100;}";
    Test
    EXECUTE FUNCTION add100(100);
    Result
    [
  200
]
    1. That is, you are creating a global function, and a function with the same name already exists within the same namespace; or, you are creating a scoped function, and a function with the same name already exists within the same scope.