You are viewing the documentation for a prerelease version.

View Latest

User-Defined Functions

  • Developer Preview
You can call a user-defined function in any expression where you can call a built-in function.

This is a Developer Preview feature, intended for development purposes only. Do not use this feature in production. No Enterprise Support is provided for Developer Preview features.

Refer to Developer Preview Mode for more information.

Description

When you have created a user-defined function, you can call it in any expression, just like a built-in function. User-defined functions have the same syntax as built-in functions, with brackets () to contain any arguments.

The name of the function may be an unqualified identifier, such as func1 or `func-1`, or a qualified identifier with a namespace, such as default:func1.

The name of a user-defined function is case-sensitive, unlike that of a built-in function. You must call the user-defined function using the same case that was used when it was created.

Arguments

A user-defined function has zero, one, or more arguments, separated by commas, just like a built-in function. Each argument is a N1QL expression required by the function.

If the function was created with named parameters, you must supply all the arguments that were specified when the function was created. If the function was created without named parameters, it is variadic, and you can supply as many arguments as needed.

Return Value

The function returns one value, of any valid N1QL type. The result (and the data type of the result) depend on the expression or code that were used to define the function.

If you supply the wrong number of arguments, or arguments with the wrong data type, the possible results differ, depending on whether the function was defined with or without any named parameters.

If the function was defined with named parameters:

  • If you do not supply enough arguments, the function generates error 10104: Incorrect number of arguments.

  • If you supply too many arguments, the function generates error 10104: Incorrect number of arguments.

  • If any of the arguments have the wrong data type, the function may return unexpected results, depending on the function expression or code.

If the function was defined without named parameters:

  • If you do not supply enough arguments, the function may return unexpected results, depending on the function expression or code.

  • If you supply too many arguments, the extra parameters are ignored.

  • If any of the arguments have the wrong data type, the function may return unexpected results, depending on the function expression or code.

Examples

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

(Refer to Inline Functions for details on creating inline user-defined functions.)

Example 1. Inline function with expression

The following statement creates a function called to_meters, which converts feet to meters.

CREATE FUNCTION to_meters() { args[0] / 0.3048 };

The following query uses the to_meters function to express the elevation of the selected airports in meters above mean sea level (mamsl). The built-in ROUND function is used to round the output to zero decimal places.

Query
SELECT airportname, ROUND(to_meters(geo.alt)) AS mamsl
FROM `travel-sample`
WHERE type = "airport"
LIMIT 5;
Result
[
  {
    "airportname": "Calais Dunkerque",
    "mamsl": 39
  },
  {
    "airportname": "Peronne St Quentin",
    "mamsl": 968
  },
  {
    "airportname": "Les Loges",
    "mamsl": 1404
  },
  {
    "airportname": "Couterne",
    "mamsl": 2356
  },
  {
    "airportname": "Bray",
    "mamsl": 1194
  }
]
Example 2. Inline function with subquery

The following statement creates a function called locations, which selects name and address information from all documents of the specified type in the travel-sample bucket.

CREATE FUNCTION locations(vType) { (
  SELECT id, name, address, city
  FROM `travel-sample`
  WHERE type = vType) };

The following query uses the locations function as the FROM term in a SELECT query. Compare this query with Example 2 in the section on FROM Subquery.

Query
SELECT l.name, l.city
FROM locations("landmark") as l
WHERE l.city = "Gillingham";
Result
[
  {
    "city": "Gillingham",
    "name": "Royal Engineers Museum"
  },
  {
    "city": "Gillingham",
    "name": "Hollywood Bowl"
  },
  {
    "city": "Gillingham",
    "name": "Thai Won Mien"
  },
  {
    "city": "Gillingham",
    "name": "Spice Court"
  },
  {
    "city": "Gillingham",
    "name": "Beijing Inn"
  },
  {
    "city": "Gillingham",
    "name": "Ossie's Fish and Chips"
  }
]