10.7. CREATE FUNCTION

Synopsis

  1. CREATE [ OR REPLACE ] 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.

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 CharacteristicDefault ValueDescription
Language clauseSQLThe language in which the function is defined.
Deterministic characteristicNOT DETERMINISTICWhether the function is deterministic. NOT DETERMINISTIC means that the function is possibly non-deterministic.
Null-call clauseCALLED ON NULL INPUTThe 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)

See Also

ALTER FUNCTION, DROP FUNCTION, SHOW FUNCTIONS