User-defined functions are a new feature provided by Couchbase 7 and up. They can be used with the connector like normal functions but with a special naming convention for using scoped functions. Normally the connector requires that functions already exist before they are used, to define them refer to the Couchbase documentation on CREATE FUNCTION queries. These may be run at the Couchbase console or with the connector in QueryPassthrough mode.
Couchbase has support for both scalar functions as well as functions that return results from subqueries. The connector supports scalar functions within its SQL dialect but subquery functions can only be used when QueryPassthrough is enabled. The rest of this section covers the connector's SQL dialect and assums that QueryPassthrough is disabled.
In both N1QL and Analytics mode, global user-defined functions can be accessed using either their simple names or their qualified names.
The simple name is just the name of the function:
SELECT ageInYears(birthdate) FROM users
Global functions may also be invoked by qualifying them with the default namespace.
Qualified names are quoted names that contain internal separators, which by default is a period though this can be changed using the DataverseSeparator property.
In both N1QL and Analytics the global namespace is called Default:
SELECT [Default.ageInYears](birthdate) FROM users
Calling global functions using simple names is recommended. While the default qualfier is supported, its only intended use is for when a UDF clashes with a standard SQL function that the connector would otherwise translate.
Both N1QL and Analytics also allow functions to be defined outside of a global context.
In Analytics functions can be attached to both dataverses and scopes which are called using two-part and three-part names respectively.
In N1QL functions may only be attached to scopes so only three-part names may be used.
/* N1QL AND Analytics */ SELECT [socialNetwork.accounts.ageInYears](birthdate) FROM [socialNetwork.accounts.users] /* Analytics only */ SELECT [socailNetwork.ageInYears](birthdate) FROM [socialNetwork.accounts.users]