9.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 ]
  9. [ DETERMINISTIC | NOT DETERMINISTIC ]
  10. [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  11. [ RETURN expression ]

Description

Create a new function with the specified definition.

Each function is uniquely identified by its qualified function nameand its parameter type list. qualified_function_name needs to be inthe 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 functionnamespace manager (See Function Namespace Managers).

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

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

A set of routine characteristics can be specified to decorate thefunction and specify its behavior. Each kind of routine characteristiccan 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 DETERMINISTICmeans that the function is possibly non-deterministic.
Null-call clauseCALLED ON NULL INPUTThe behavior of the function in which null is supplied asthe 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 alreadyexist, adding a function description and explicitly listing all the supportedroutine 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