Define SQL Functions
Presto supports user defined expressions as SQL functions, which are dynamic functions separate from Presto source code that are managed by the function namespace manager.
Function Namespace Manager
The Function Namespace Manager is a plugin that manages a set of these function catalog schemas. User defined function management is separated from connector API for flexibility so these SQL functions can be used across all connectors. The query is guaranteed to use the same version of the function throughout the execution and any modification to the functions is versioned.
Function Namespace
The function namespace is a special catalog.schema
format that stores functions. By default, an ahana.default
function namespace will be created for all Ahana clusters.
Create a SQL function
Here is an example of a SQL function for COSECANT:
CREATE OR REPLACE FUNCTION ahana.default.cosec(x double)
RETURNS double
COMMENT 'Cosecant trigonometric function'
LANGUAGE SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
RETURN 1 / sin(x);
Apply the user defined function in a SQL query
The fully qualified function name is required while using in a SQL query.
Here is an example of using the cosec
user defined function in a SQL query:
presto> SELECT ahana.default.cosec (50) AS Cosec_value;
Cosec_value
---------------------
-3.8113408578721053 (1 row)