docs/_docs/SQL/custom-sql-func.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Custom SQL Functions

:javaFile: {javaCodeDir}/SqlAPI.java

The SQL Engine can extend the SQL functions' set, defined by the ANSI-99 specification, via the addition of custom SQL functions written in Java.

A custom SQL function is just a `public` or `public static` method marked by the `@QuerySqlFunction` annotation.

////
TODO looks like it's unsupported in C#
////


[source,java]
----
include::{javaFile}[tags=sql-function-example, indent=0]
----


The class that owns the custom SQL function has to be registered in the `CacheConfiguration`.
To do that, use the `setSqlFunctionClasses(...)` method.

[source,java]
----
include::{javaFile}[tags=sql-function-config, indent=0]
----

Once you have deployed a cache with the above configuration, you can call the custom function from within SQL queries:

[source,java]
----
include::{javaFile}[tags=sql-function-query, indent=0]
----


Custom SQL function can be a table function. Result of table function is treated as a row set (a table) and can be used
by other SQL operators. Custom SQL function is also a `public` or `public static` method marked by annotation `@QuerySqlTableFunction`.
Table function must return an `Iterable` as a row set. Each row can be represented by an `Object[]` or by a `Collection`.
Row length must match the defined number of column types. Row value types must match the defined column types or be able
assigned to them.

[source,java]
----
include::{javaFile}[tags=sql-table-function-example, indent=0]
----
[source,java]
----
include::{javaFile}[tags=sql-table-function-config-query, indent=0]
----

With custom SQL function you can get access to application attributes set on the client side via link:session-context#sql-function[SessionContext API].

NOTE: Creating custom functions in the system schema is forbidden. Creating a custom function in schema 'PUBLIC' is forbidden if the function name interferes with a standard function like 'TYPEOF' or 'SUBSTRING'.

NOTE: The table functions and application attributes are currently available only with link:SQL/sql-calcite[Calcite, window=_blank].

NOTE: Classes registered with `CacheConfiguration.setSqlFunctionClasses(...)` must be added to the classpath of all the nodes where the defined custom functions might be executed. Otherwise, you will get a `ClassNotFoundException` error when trying to execute the custom function.