CREATE FUNCTION

concept

Enterprise Edition
Couchbase Server 7.0

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

Purpose

There are two types of user-defined function:

Inline functions are defined using N1QL expressions, including subqueries. 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 N1QL expressions. The only supported language is JavaScript.

Global Functions and Scope Functions

You can create user-defined functions at two different levels of the N1QL 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 scope function is created within a scope, at the same level as the collections within the scope. When you call a scope 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 scope 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 scope 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 scope function stored in the default scope in a bucket.

Syntax

The CREATE FUNCTION statement takes a different syntax depending on the type of function you are creating.

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

create-function-inline | create-function-external

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 name
                          '(' [ parameter ( ',' parameter )* ] ')'
                           ( '{' expression '}' | LANGUAGE INLINE AS expression )

'CREATE' ( 'OR' 'REPLACE' )? 'FUNCTION' name '(' (parameter (',' parameter)* )? ')' ( '{' expression '}' | 'LANGUAGE' 'INLINE' 'AS' expression )

name: The name of the function. 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 scope 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 scope 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.

parameter: [Optional] The name of a function argument. 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.

expression

The N1QL expression that defines the function. You can use any valid 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 N1QL 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.

Replacing a Function

The optional OR REPLACE keywords enable you to redefine a function. If these keywords are present, and a function with the same name already exists, the existing function is replaced.

If these keywords are not present, and a function with the same name already exists, an error is generated.

External Functions

create-function-external ::= CREATE [ OR REPLACE ] FUNCTION name
                             '(' [ parameter ( ',' parameter )* ] ')'
                             LANGUAGE JAVASCRIPT AS object AT library

'CREATE' ( 'OR' 'REPLACE' )? 'FUNCTION' name '(' (parameter (',' parameter)* )? ')' 'LANGUAGE' 'JAVASCRIPT' 'AS' object 'AT' library

name: The name of the function. 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 scope 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 scope 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.

parameter: [Optional] The name of a function argument. 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.

object: 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 functions in N1QL only support plain JavaScript, without any of the added language features supported by functions in the Eventing Service.

library: The name of the JavaScript library that contains the function you want to use. This parameter is a string and must be wrapped in quotes. You must create the JavaScript library and the JavaScript function using the N1QL Functions REST API. For details, refer to Functions REST API.

Replacing a Function

The optional OR REPLACE keywords enable you to redefine a function. If these keywords are present, and a function with the same name already exists, the existing function is replaced.

If these keywords are not present, and a function with the same name already exists, an error is generated.

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.

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 `travel-sample`.inventory.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. External functions

The following command registers two JavaScript functions called encodeGeoHash and calculateAdjacent in a library called geohash-js. ^[1]

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.

curl -v -X POST \
http://localhost:8093/evaluator/v1/libraries/geohash-js \
-u Administrator:password \
-H 'content-type: application/json' \
-d '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 http://geohash.org/gcqrs0z2jfdr and follow one of the links provided. 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 http://geohash.org/gcqrs0z2jff2 and follow one of the links provided. At this level of precision, the geohash should appear to be in almost exactly the same location as the previous one.

To execute user-defined functions, refer to EXECUTE FUNCTION.
To include user-defined functions in an expression, refer to User-Defined Functions.
To view user-defined functions, refer to Monitor Queries.
To drop user-defined functions, refer to DROP FUNCTION.

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

CREATE FUNCTION

Purpose

Global Functions and Scope Functions

Syntax

Inline Functions

Replacing a Function

External Functions

Replacing a Function

Examples

Related Links