Presto 0.237.1 Documentation

10.7. CREATE FUNCTION

10.7. CREATE FUNCTION

Synopsis

CREATE [ OR REPLACE ] FUNCTION
qualified_function_name (
  parameter_name parameter_type
  [, ...]
)
RETURNS return_type
[ COMMENT function_description ]
[ LANGUAGE SQL ]
[ DETERMINISTIC | NOT DETERMINISTIC ]
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
[ RETURN expression ]

Description

Create a new function with the specified definition.

Each function is uniquely identified by its qualified function name and its parameter type list. qualified_function_name needs to be in the format of catalog.schema.function_name.

In order to create a function, the corresponding function namespace (in the format catalog.schema) must first be managed by a function namespace manager (See Function Namespace Managers).

The optional OR REPLACE clause causes the query to quietly replace the existing function if a function with the identical signature (function name with parameter type list) exists.

The return_type needs to match the actual type of the routine body expression, without performing type coercion.

A set of routine characteristics can be specified to decorate the function and specify its behavior. Each kind of routine characteristic can be specified at most once.

Routine Characteristic Default Value Description
Language clause SQL The language in which the function is defined.
Deterministic characteristic NOT DETERMINISTIC Whether the function is deterministic. NOT DETERMINISTIC means that the function is possibly non-deterministic.
Null-call clause CALLED ON NULL INPUT The behavior of the function in which null is supplied as the value of at least one argument.

Examples

Create a new function example.default.tan(double):

CREATE FUNCTION example.default.tan(x double)
RETURNS double
DETERMINISTIC
RETURNS NULL ON NULL INPUT
RETURN sin(x) / cos(x)

Create the table example.default.tan(double) if it does not already exist, adding a function description and explicitly listing all the supported routine characteristics:

CREATE OR REPLACE FUNCTION example.default.tan(x double)
RETURNS double
COMMENT 'tangent trigonometric function'
LANGUAGE SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
RETURN sin(x) / cos(x)