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 andNULL
will always be returned. If the return type of the function is notVOID
, the last statement of a UDF must be aSELECT
. - The volatility indicates whether the function has side effects.
VOLATILE
andNOT 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
orIMMUTABLE
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 precedeLEAKPROOF
withIMMUTABLE
, and onlyIMMUTABLE
can be set toLEAKPROOF
.NOT LEAKPROOF
is allowed with any other volatility.
- Annotate a function with side effects with
- 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.
- Can reference arguments by name or by their ordinal in the function definition with the syntax
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:
CREATE FUNCTION add(a INT, b INT) RETURNS INT IMMUTABLE LEAKPROOF LANGUAGE SQL AS 'SELECT a + b';
Where:
- name:
add
- arguments:
a
of typeINT
,b
of typeINT
- return type:
INT
- volatility:
IMMUTABLE LEAKPROOF
- language:
SQL
- function body:
'SELECT a + b'
Alternatively, you could define this function as:
CREATE FUNCTION add(a INT, b INT) RETURNS INT IMMUTABLE LEAKPROOF LANGUAGE SQL AS 'SELECT $1 + $2';
Or as:
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:
SELECT add(3,5) as sum;
sum
-------
8
(1 row)
View a UDF definition
To view the add
function definition, run:
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
Limitations on use of UDFs
User-defined functions are not currently supported in:
Expressions (column, index, constraint) in tables.
Views.
Other user-defined functions.
Limitations on expressions allowed within UDFs
The following are not currently allowed within the body of a UDF:
Subqueries in statements.
Mutation statements such as
INSERT
,UPDATE
,DELETE
, andUPSERT
.Expressions with
*
such asSELECT *
.CTEs (common table expressions).
References to other user-defined functions.