User-Defined Functions

On this page Carat arrow pointing down

New in v22.2: A user-defined function (UDF) is a named function defined at the database level that can be called in queries and other contexts. CockroachDB supports invoking UDFs in SELECT, FROM, and WHERE clauses of DML statements.

Overview

The basic components of a user-defined function are a name, list of arguments, return type, volatility, language, and function body.

  • An argument has a mode and a type. CockroachDB supports the IN argument mode. The type can be a built-in type, user-defined enum, or implicit record type. CockroachDB does not support default values for arguments.
  • The return type can be a built-in type, user-defined enum, implicit record type, or VOID. VOID indicates that there is no return type and NULL will always be returned. If the return type of the function is not VOID, the last statement of a UDF must be a SELECT.
  • The volatility indicates whether the function has side effects. VOLATILE and NOT LEAKPROOF are the default.
    • Annotate a function with side effects with VOLATILE. This also prevents the cost-based optimizer from pre-evaluating the function.
    • A STABLE or IMMUTABLE function does not mutate data.
    • LEAKPROOF indicates that a function has no side effects and that it communicates nothing that depends on its arguments besides the return value (i.e., it cannot throw an error that depends on the value of its arguments). You must precede LEAKPROOF with IMMUTABLE, and only IMMUTABLE can be set to LEAKPROOF. NOT LEAKPROOF is allowed with any other volatility.
  • The language specifies the language of the function body. CockroachDB supports the language SQL.
  • The function body:
    • Can reference arguments by name or by their ordinal in the function definition with the syntax $1.
    • Can be enclosed in a single line with single quotes '' or multiple lines with $$.
    • Can reference tables.
    • Can reference only the SELECT statement.

Examples

The following examples show how to create and invoke a simple UDF and view the definition of the UDF.

Create a UDF

The following is a UDF that returns the sum of two integers:

icon/buttons/copy
CREATE FUNCTION add(a INT, b INT) RETURNS INT IMMUTABLE LEAKPROOF LANGUAGE SQL AS 'SELECT a + b';

Where:

  • name: add
  • arguments: a of type INT, b of type INT
  • return type: INT
  • volatility: IMMUTABLE LEAKPROOF
  • language: SQL
  • function body: 'SELECT a + b'

Alternatively, you could define this function as:

icon/buttons/copy
CREATE FUNCTION add(a INT, b INT) RETURNS INT IMMUTABLE LEAKPROOF LANGUAGE SQL AS 'SELECT $1 + $2';

Or as:

icon/buttons/copy
CREATE FUNCTION add(a INT, b INT) RETURNS INT LANGUAGE SQL AS $$
  SELECT a + b;
$$;

Invoke a UDF

You invoke the UDF like a built-in function:

icon/buttons/copy
SELECT add(3,5) as sum;
  sum
-------
    8
(1 row)

View a UDF definition

To view the add function definition, run:

icon/buttons/copy
SHOW CREATE FUNCTION add;

If you do not specify a schema for the function add when you create it, the default schema is public:

  function_name |                 create_statement
----------------+---------------------------------------------------
 add            | CREATE FUNCTION public.add(IN a INT8, IN b INT8)
                |     RETURNS INT8
                |     IMMUTABLE
                |     LEAKPROOF
                |     CALLED ON NULL INPUT
                |     LANGUAGE SQL
                |     AS $$
                |     SELECT a + b;
                | $$
(1 row)

Known limitations

User-defined functions are not supported in:

  • Expressions (column, index, constraint) in tables.
  • Views.
  • Other user-defined functions.

  • CDC transformations do not support user-defined functions

See also


Yes No
On this page

Yes No