You can call a user-defined function in any expression where you can call a built-in function.
RBAC Privileges
To execute global internal functions, you must have the Execute Global Functions role. To execute scoped internal functions, you must have the Execute Scope Functions role, with permissions on the specified bucket and scope.
To execute global external functions, you must have the Execute Global External Functions role. To execute scoped external functions, you must have the Execute Scope External Functions role, with permissions on the specified bucket and scope.
For more details about user roles, see Authorization.
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 is usually an unqualified identifier, such as func1
or `func-1`
.
In this case, the path to the function is determined by the current query context.
To call a global function in a particular namespace, the function name must be a qualified identifier with a namespace, such as default:func1
.
Similarly, to call 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
.
Refer to Global Functions and Scoped Functions for more details.
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. |
It is not possible to call a user-defined function in an expression if the function has side effects, such as performing mutations. When you do this, an error is generated.
Arguments
A user-defined function has zero, one, or more arguments, separated by commas, just like a built-in function. Each argument is a SQL++ 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, you cannot supply an argument. If the function is variadic, you can supply as many arguments as needed, or none.
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 is variadic, or requires a definite number of arguments.
If the function requires a definite number of arguments:
-
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 is variadic:
-
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
Refer to CREATE FUNCTION for details on creating user-defined functions.
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 use the examples on this page, you must set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
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.
SELECT airportname, ROUND(to_meters(geo.alt)) AS mamsl
FROM airport
LIMIT 5;
[
{
"airportname": "Calais Dunkerque",
"mamsl": 4
},
{
"airportname": "Peronne St Quentin",
"mamsl": 90
},
{
"airportname": "Les Loges",
"mamsl": 130
},
{
"airportname": "Couterne",
"mamsl": 219
},
{
"airportname": "Bray",
"mamsl": 111
}
]
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) };
The following query uses the locations
function as the FROM term in a SELECT query.
Compare this query with Example 2 in FROM Subquery.
SELECT l.name, l.city
FROM locations("eat") AS l
WHERE l.city = "Gillingham";
[
{
"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"
}
]
For this example, it is assumed that you have created two external functions:
-
A function called
geohash
, which depends on the JavaScriptencodeGeoHash
function in thegeohash-js
library; -
A function called
adjacent
, which depends on the JavaScriptcalculateAdjacent
function in thegeohash-js
library.
Refer to Example 7 in CREATE FUNCTION for details.
The following query uses the geohash
and adjacent
functions to find the 9-figure geohash of the selected hotel, and the geohashes immediately to the north, south, west, and east.
SELECT area,
adjacent(area, "top") AS north,
adjacent(area, "bottom") AS south,
adjacent(area, "left") AS west,
adjacent(area, "right") AS east
FROM hotel
LET area = SUBSTR(geohash(geo.lat, geo.lon), 0, 9)
WHERE name = "Sachas Hotel";
[
{
"area": "gcw2m05h1",
"east": "gcw2m05h4",
"north": "gcw2m05h3",
"south": "gcw2m055c",
"west": "gcw2m05h0"
}
]
To view the first geohash on a map, go to http://geohash.org/gcw2m05h1 and follow one of the links provided. You can view the other geohashes by editing the URL. At the latitude of the selected hotel, each geohash represents an area of approximately 3 𐄂 5 meters.
Related Links
-
To create user-defined functions, refer to CREATE FUNCTION.
-
To execute user-defined functions, refer to EXECUTE FUNCTION.
-
To view user-defined functions, refer to Monitor Queries.
-
To drop user-defined functions, refer to DROP FUNCTION.