Skip to main content

Define SQL Functions

Presto support user defined expressions as SQL functions, these are dynamic functions separated from Presto Source code and managed by the function namespace manager.

Function Namespace Manager

It is a plugin that manages a set of these function catalog schemas. This user defined function management is separated from connector API for flexibility, hence these SQL functions can be used across all connectors. Further, 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

It is a special catalog.schema format that stores functions, by defaut ahana.default function namespace will be created for all Ahana clusters.

Step 1: Create a SQL function#

Create a function

Here is simple example of 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);

Step 2: Apply the newly created function with SQL query#

Apply the newly created function

It is required for users to use fully qualified function name while using in SQL query.

Following the the example of using cosec SQL function in the query.

presto> SELECT ahana.default.cosec (50) AS Cosec_value;
Cosec_value
---------------------
-3.8113408578721053 (1 row)