FUNCTION#

Synopsis#

FUNCTION name ( [ parameter_name data_type [, ...] ] )
  RETURNS type
  [ LANGUAGE language]
  [ NOT? DETERMINISTIC ]
  [ RETURNS NULL ON NULL INPUT ]
  [ CALLED ON NULL INPUT ]
  [ SECURITY { DEFINER | INVOKER } ]
  [ COMMENT description]
  [ WITH ( property_name = expression [, ...] ) ]
  { statements | AS definition }

Description#

Declare a user-defined function.

The name of both Inline UDFs and Catalog UDFs can be a simple string without spaces or punctuation. In Starburst Galaxy, if you prefer to specify the full path as the nam, it must be galaxy.functions.name.

The list of parameters is a comma-separated list of names parameter_name and data types data_type (see data type). An empty list, specified as () is also valid.

The type value after the RETURNS keyword identifies the data type of the UDF output.

The optional LANGUAGE characteristic identifies the language used for the UDF definition with language. For Starburst Galaxy, the supported value for language is SQL. If not specified, the default language is SQL.

The optional DETERMINISTIC or NOT DETERMINISTIC characteristic declares that the UDF is deterministic. This means that repeated UDF calls with identical input parameters yield the same result. A UDF is non-deterministic if it calls any non-deterministic UDFs and functions. By default, UDFs are assumed to have a deterministic behavior.

The optional RETURNS NULL ON NULL INPUT characteristic declares that the UDF returns a NULL value when any of the input parameters are NULL. The UDF is not invoked with a NULL input value.

The CALLED ON NULL INPUT characteristic declares that the UDF is invoked with NULL input parameter values.

The RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT characteristics are mutually exclusive, with CALLED ON NULL INPUT as the default.

The security declaration of SECURITY INVOKER or SECURITY DEFINER is only valid for catalog UDFs. It sets the mode for processing the UDF with the permissions of the user who calls the UDF (INVOKER) or the user who created the UDF (DEFINER).

The COMMENT characteristic can be used to provide information about the function to other users as description. The information is accessible with SHOW FUNCTIONS.

The optional WITH clause can be used to specify properties for the function. The available properties vary based on the function language.

For SQL UDFs the body of the UDF can either be a simple single RETURN statement with an expression, or compound list of statements in a BEGIN block. UDF must contain a RETURN statement at the end of the top-level block, even if it is unreachable.

For UDFs in future non-SQL languages, the definition is enclosed in a $$-quoted string.

Examples#

A simple catalog function:

CREATE FUNCTION galaxy.default.meaning_of_life()
  RETURNS BIGINT
  RETURN 42;

And used:

SELECT galaxy.default.meaning_of_life(); -- returns 42

Equivalent usage with an inline function:

WITH FUNCTION meaning_of_life()
  RETURNS BIGINT
  RETURN 42
SELECT meaning_of_life();

Further examples of varying complexity that cover usage of the FUNCTION statement in combination with other statements are available in the SQL UDF documentation.

See also#