CREATE FUNCTION

Synopsis

  1. CREATE [ OR REPLACE ] [TEMPORARY] FUNCTION
  2. qualified_function_name (
  3. parameter_name parameter_type
  4. [, ...]
  5. )
  6. RETURNS return_type
  7. [ COMMENT function_description ]
  8. [ LANGUAGE [ SQL | identifier] ]
  9. [ DETERMINISTIC | NOT DETERMINISTIC ]
  10. [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  11. [ RETURN expression | EXTERNAL [ NAME identifier ] ]

Description

Create a new function with the specified definition.

When TEMPORARY is specified, the created function is valid and visible within the current session, but no persistent entry is made.

Each permanent 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.

Each temporary functions is uniquely identified by the function name. The name cannot be qualified, or collide with the name of an existing built-in function.

In order to create a permanent 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):

  1. CREATE FUNCTION example.default.tan(x double)
  2. RETURNS double
  3. DETERMINISTIC
  4. RETURNS NULL ON NULL INPUT
  5. 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:

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

Create a new temporary function square:

  1. CREATE TEMPORARY FUNCTION square(x int)
  2. RETURNS int
  3. RETURN x * x

See Also

ALTER FUNCTION, DROP FUNCTION, SHOW FUNCTIONS