Skip to main content

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​

Create a 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​

Apply the newly created function

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)