CREATE FUNCTION

Capella Operational

reference

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

Purpose

Capella supports 2 types of user-defined function in SQL++ for Query:

Inline functions are defined using SQL++ or JavaScript expressions. Use an inline function to reuse complex or repetitive expressions, including subqueries, and simplify your SQL++ queries.
External functions are defined using an external language and stored in a user-defined function (UDF) library. External functions can be collectively managed through their UDF library. Capella supports defining external functions using JavaScript.

You can use inline or external JavaScript functions to run expressions or queries that may be difficult or impossible to define using built-in SQL++ expressions.

JavaScript functions in the Query Service 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 Functions for Query Reference.

SQL++ Managed User-Defined Functions

Couchbase Server 7.6

For operational clusters using Couchbase Server 7.6 and later, you can create the code for a JavaScript function and the corresponding SQL++ user-defined function in a single operation in the Query Tab or cbq. You do not have to create a UDF library before creating a SQL++ user-defined function.

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

External Libraries

You can store JavaScript functions in a user-defined function (UDF) library. This enables you to share external function code for use in more than one SQL++ user-defined function. A library can contain 1 or more JavaScript functions.

For more information about how to create a UDF library, see Create a JavaScript Library.

UDF libraries, like SQL++ user-defined functions, may be scoped or global. Set a UDF library or user-defined function as Scoped to keep the code for external functions separate.

Any code that you store in a global library is available to all users with read and write permissions on your operational cluster.

A global library may have the same name as a scoped library, and scoped libraries may have the same name as each other. For example, you can have a global math library, and a math library in each scope.

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’s 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.

A global function is not the same as a scoped function stored in the default scope in a bucket.

Prerequisites

To execute this statement, your client must have necessary privileges depending on your cluster access credential type and whether the function is global or scoped.

Credential Type Function Type Privilege

Credential Type	Function Type	Privilege
Basic	Global or scoped	`Write`
Advanced	Global	`Global Function Manage`
Advanced	Scoped	`Query Manage`

Basic

Global or scoped

Write

Advanced

Global

Global Function Manage

Advanced

Scoped

Query Manage

Syntax

The CREATE FUNCTION statement takes a different syntax depending on the type of function you’re creating. See Inline Functions or JavaScript Functions below.

create-function ::= create-function-inline | create-function-external

Inline Functions

The CREATE FUNCTION statement provides two possible 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 )

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

The function name specifies the name of the function to create. It’s 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.

The function name must be unique within its scope or namespace. You cannot have two functions with the same name inside the same scope or namespace. You can have two functions with the same name across different scopes or namespaces.

Function Parameters

params ::= identifier ( "," identifier )* | "..."

[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 (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.

JavaScript Functions

The CREATE FUNCTION statement provides two possible syntaxes for defining an external function: one that references a function code in a JavaScript library, and one that creates a SQL++ managed JavaScript function.

create-function-external ::= 'CREATE' ( 'OR' 'REPLACE' )? 'FUNCTION' function '(' params? ')'
                             ( 'IF' 'NOT' 'EXISTS' )?
                             'LANGUAGE' 'JAVASCRIPT' 'AS' ( obj 'AT' library | javascript )

function	Function Name
params	Function Parameters
obj	External Object
library	External Library
javascript	Function Body

OR REPLACE / IF NOT EXISTS

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

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 )* | "..."

External Object

[Optional] Use this parameter when the function code is stored in a JavaScript library.

The name of the JavaScript function that you want to use for the user-defined function. This parameter is a string and must be wrapped in quotes.

External Library

[Optional] Use this parameter when the function code is stored in a JavaScript library.

The name of the JavaScript library that contains the JavaScript function you want to use. This parameter is a string and must be wrapped in quotes.

The name of a scoped JavaScript library must include the bucket name, the scope name, and the library name, separated by slashes. For example, to refer to a scoped library called my-library located in the inventory scope within the travel-sample bucket, you would specify the library name as travel-sample/inventory/my-library.

Function Body

[Optional] Use this parameter to create a SQL++ managed JavaScript 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.

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.

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.

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Example 1. Inline function with the LANGUAGE syntax

This statement creates a function called celsius, which converts Fahrenheit to Celsius. The function takes a single argument.

CREATE FUNCTION celsius(fahrenheit) LANGUAGE INLINE AS (fahrenheit - 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 takes a single argument.

CREATE FUNCTION fahrenheit(celsius) { (celsius * 9/5) + 32 };

Test

EXECUTE FUNCTION fahrenheit(100);

Result

[
  212
]

Example 3. Inline function with multiple 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 multiple 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. Variadic inline function

The following statement creates a function called total_length, which takes a variable number of strings and returns the total length of all the strings. As the function is variadic, you can use any number of arguments when you call the function.

CREATE FUNCTION total_length(...)
{ ARRAY_SUM((SELECT VALUE LEN(a) FROM args AS a)) };

Test

EXECUTE FUNCTION total_length("Hello", "Goodbye");

Result

[
  12
]

Example 7. 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 8. SQL++ managed JavaScript 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
]

Example 9. External functions

The following code defines two JavaScript functions called encodeGeoHash and calculateAdjacent in a library called geohash-js. ^[2]

The function encodeGeoHash takes two arguments, a latitude and a longitude, and returns the 12-character geohash for the specified location.
The function calculateAdjacent takes two arguments, a geohash and a direction — "top", "bottom", "left", or "right" — and returns the geohash of the location next to the original geohash in the specified direction.

function encodeGeoHash(latitude, longitude) {

  var BITS = [16, 8, 4, 2, 1];
  var BASE32 = "0123456789bcdefghjkmnpqrstuvwxyz";

  var is_even = 1;
  var i = 0, mid;
  var lat = []; var lon = [];
  var bit = 0;
  var ch = 0;
  var precision = 12;
  var geohash = "";

  lat[0] = -90.0; lat[1] = 90.0;
  lon[0] = -180.0; lon[1] = 180.0;

  while (geohash.length < precision) {
    if (is_even) {
      mid = (lon[0] + lon[1]) / 2;
      if (longitude > mid) {
        ch |= BITS[bit];
        lon[0] = mid;
      } else
        lon[1] = mid;
    } else {
      mid = (lat[0] + lat[1]) / 2;
      if (latitude > mid) {
        ch |= BITS[bit];
        lat[0] = mid;
      } else
        lat[1] = mid;
    }

    is_even = !is_even;
    if (bit < 4)
      bit++;
    else {
      geohash += BASE32[ch];
      bit = 0;
      ch = 0;
    }
  }

  return geohash;
}

function calculateAdjacent(srcHash, dir) {

  var BITS = [16, 8, 4, 2, 1];
  var BASE32 = "0123456789bcdefghjkmnpqrstuvwxyz";

  var NEIGHBORS = { right  : { even : "bc01fg45238967deuvhjyznpkmstqrwx" },
                    left   : { even : "238967debc01fg45kmstqrwxuvhjyznp" },
                    top    : { even : "p0r21436x8zb9dcf5h7kjnmqesgutwvy" },
                    bottom : { even : "14365h7k9dcfesgujnmqp0r2twvyx8zb" } };

  var BORDERS   = { right  : { even : "bcfguvyz" },
                    left   : { even : "0145hjnp" },
                    top    : { even : "prxz" },
                    bottom : { even : "028b" } };

  NEIGHBORS.bottom.odd = NEIGHBORS.left.even;
  NEIGHBORS.top.odd = NEIGHBORS.right.even;
  NEIGHBORS.left.odd = NEIGHBORS.bottom.even;
  NEIGHBORS.right.odd = NEIGHBORS.top.even;

  BORDERS.bottom.odd = BORDERS.left.even;
  BORDERS.top.odd = BORDERS.right.even;
  BORDERS.left.odd = BORDERS.bottom.even;
  BORDERS.right.odd = BORDERS.top.even;

  srcHash = srcHash.toLowerCase();
  var lastChr = srcHash.charAt(srcHash.length - 1);
  var type = (srcHash.length % 2) ? "odd" : "even";
  var base = srcHash.substring(0, srcHash.length - 1);
  if (BORDERS[dir][type].indexOf(lastChr) != -1)
    base = calculateAdjacent(base, dir);
  return base + BASE32[NEIGHBORS[dir][type].indexOf(lastChr)];
}

The following statements create two functions:

A function called geohash, which calls the JavaScript encodeGeoHash function from the geohash-js library;
A function called adjacent, which calls the JavaScript calculateAdjacent function from the geohash-js library.

CREATE FUNCTION geohash(lat, lon)
  LANGUAGE JAVASCRIPT AS "encodeGeoHash" AT "geohash-js";

CREATE FUNCTION adjacent(src, dir)
  LANGUAGE JAVASCRIPT AS "calculateAdjacent" AT "geohash-js";

Test geohash

EXECUTE FUNCTION geohash(53.353744, -2.27495);

Result

[
  "gcqrs0z2jfdr"
]

To view the geohash on a map, go to Geohashes and enter the string in the Geohash box. At the specified latitude, the geohash represents an area of approximately 11 𐄂 19 millimeters.

Test adjacent

EXECUTE FUNCTION adjacent(geohash(53.353744, -2.27495), "top");

Result

[
  "gcqrs0z2jff2"
]

To view the geohash on a map, go to Geohashes and enter the string in the Geohash box. At this level of precision, the geohash should appear to be in almost exactly the same location as the previous one.

For an introduction to user-defined functions, see User-Defined Functions for Queries.
For more information about JavaScript functions, see JavaScript Functions for Query Reference.
To execute a user-defined function, see EXECUTE FUNCTION.
To see the execution plan for a user-defined function, see EXPLAIN FUNCTION.
To include a user-defined function in an expression, see User-Defined Functions.
To monitor user-defined functions, see Monitor Functions.
To drop a user-defined function, see DROP FUNCTION.

1. In other words, you’re creating a global function, and a function with the same name already exists within the same namespace; or, you’re creating a scoped function, and a function with the same name already exists within the same scope.

2. Credit: https://github.com/davetroy/geohash-js

CREATE FUNCTION

Purpose

SQL++ Managed User-Defined Functions

External Libraries

Global Functions and Scoped Functions

Prerequisites

Syntax

Inline Functions

OR REPLACE / IF NOT EXISTS

Function Name

Function Parameters

Function Body

JavaScript Functions

OR REPLACE / IF NOT EXISTS

Function Name

Function Parameters

External Object

External Library

Function Body

Examples

Related Links