User-Defined Functions

• Functions return a value, and procedures do not return a value.
• Procedures must be invoked using a CALL statement. Functions can be invoked in nearly any context, such as SELECT, FROM, and WHERE clauses, DEFAULT expressions, and computed column expressions.
• Functions have volatility settings, and procedures do not.

​

Overview

• 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 SQL type, user-defined ENUM, RECORD, PL/pgSQL REFCURSOR type, implicit record type, or VOID.
  • Preceding a type with SETOF indicates that a set, or multiple rows, may be returned. For an example, see Create a function that returns a set of results.
  • VOID indicates that there is no return type and NULL will always be returned.
• 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. You cannot create a STABLE or IMMUTABLE function that executes a mutation ( INSERT, UPSERT, UPDATE, DELETE ) statement.
  • 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.
  • Non- VOLATILE functions can be optimized through inlining. For more information, see Create an inlined UDF.
• LANGUAGE specifies the language of the function body. CockroachDB supports the languages SQL and PLpgSQL (PL/pgSQL).
• 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

​

Create a UDF

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

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

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

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

​

View a UDF definition

SHOW CREATE FUNCTION add;

  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)

​

Invoke a UDF

SELECT add(3,5) as sum;

  sum
-------
    8
(1 row)

​

Create a UDF using PL/pgSQL

CREATE FUNCTION fib(n int) RETURNS INT AS $$
    DECLARE
        tmp INT;
        a INT := 0;
        b INT := 1;
        i INT := 2;
    BEGIN
        IF n < 0 THEN
            RAISE EXCEPTION 'n must be non-negative';
        END IF;
        IF n = 0 THEN RETURN 0; END IF;
        IF n = 1 THEN RETURN 1; END IF;
        LOOP
            IF i > n THEN EXIT; END IF;
            tmp := a + b;
            a := b;
            b := tmp;
            i := i + 1;
        END LOOP;
        RETURN b;
    END
  $$ LANGUAGE PLpgSQL;

SELECT fib(8);

  fib
-------
   21

​

Create an inlined UDF

• It is labeled as IMMUTABLE, STABLE, or LEAKPROOF (i.e., non- VOLATILE ).
• It has a single statement.
• It is not a set-returning function.
• Its arguments are only variable or constant expressions.
• It is not a record-returning function.

1. Create tables a and b:
   CREATE TABLE a (
     a INT
   );
   
   CREATE TABLE b (
     b INT PRIMARY KEY
   );
   
2. Insert a value (10) into 1000 rows in a and 1 row in b:
   INSERT INTO a SELECT 10 FROM generate_series(1, 1000);
   INSERT INTO b VALUES (10);
   
3. Create a VOLATILE function foo_v() and a STABLE function foo_s():
   CREATE FUNCTION foo_v(x INT) RETURNS INT VOLATILE LANGUAGE SQL AS $$
     SELECT b FROM b WHERE b = x
   $$;
   
   CREATE FUNCTION foo_s(x INT) RETURNS INT STABLE LANGUAGE SQL AS $$
     SELECT b FROM b WHERE b = x
   $$;
   
   Each function returns a specified value from table b.
4. View the query plan when foo_v() (the VOLATILE function) is used in a selection query to retrieve equal values from table a:
   EXPLAIN ANALYZE SELECT foo_v(a) FROM a WHERE a = 10;
   
                                               info
   --------------------------------------------------------------------------------------------
     planning time: 2ms
     execution time: 77ms
     distribution: local
     vectorized: true
     rows read from KV: 1,000 (39 KiB, 1 gRPC calls)
     cumulative time spent in KV: 330µs
     maximum memory usage: 80 KiB
     network usage: 0 B (0 messages)
     sql cpu time: 75ms
     estimated RUs consumed: 0
   
     • render
     │
     └── • filter
         │ nodes: n1
         │ actual row count: 1,000
         │ sql cpu time: 75ms
         │ estimated row count: 1,000
         │ filter: a = 10
         │
         └── • scan
               nodes: n1
               actual row count: 1,000
               KV time: 330µs
               KV contention time: 0µs
               KV rows read: 1,000
               KV bytes read: 39 KiB
               KV gRPC calls: 1
               estimated max memory allocated: 60 KiB
               sql cpu time: 87µs
               estimated row count: 1,000 (100% of the table; stats collected 19 seconds ago)
               table: a@a_pkey
               spans: FULL SCAN
   (33 rows)
   
   The query takes 77ms to execute because the function is invoked for each row scanned in table a.
5. View the query plan when using foo_s() (the STABLE function) instead:
   EXPLAIN ANALYZE SELECT foo_s(a) FROM a WHERE a = 10;
   
                                                 info
   ------------------------------------------------------------------------------------------------
     planning time: 5ms
     execution time: 4ms
     distribution: local
     vectorized: true
     rows read from KV: 1,001 (39 KiB, 2 gRPC calls)
     cumulative time spent in KV: 832µs
     maximum memory usage: 420 KiB
     network usage: 0 B (0 messages)
     sql cpu time: 3ms
     estimated RUs consumed: 0
   
     • render
     │
     └── • merge join (left outer)
         │ nodes: n1
         │ actual row count: 1,000
         │ estimated max memory allocated: 340 KiB
         │ estimated max sql temp disk usage: 0 B
         │ sql cpu time: 3ms
         │ estimated row count: 1,000
         │ equality: (a) = (b)
         │ right cols are key
         │
         ├── • filter
         │   │ nodes: n1
         │   │ actual row count: 1,000
         │   │ sql cpu time: 5µs
         │   │ estimated row count: 1,000
         │   │ filter: a = 10
         │   │
         │   └── • scan
         │         nodes: n1
         │         actual row count: 1,000
         │         KV time: 722µs
         │         KV contention time: 0µs
         │         KV rows read: 1,000
         │         KV bytes read: 39 KiB
         │         KV gRPC calls: 1
         │         estimated max memory allocated: 60 KiB
         │         sql cpu time: 202µs
         │         estimated row count: 1,000 (100% of the table; stats collected 42 seconds ago)
         │         table: a@a_pkey
         │         spans: FULL SCAN
         │
         └── • scan
               nodes: n1
               actual row count: 1
               KV time: 110µs
               KV contention time: 0µs
               KV rows read: 1
               KV bytes read: 30 B
               KV gRPC calls: 1
               estimated max memory allocated: 20 KiB
               sql cpu time: 11µs
               estimated row count: 1 (100% of the table; stats collected 42 seconds ago)
               table: b@b_pkey
               spans: FULL SCAN
   (57 rows)
   
   The query takes only 4ms to execute because the function is inlined and transformed to a join with an equality comparison (a) = (b), which has much less overhead than invoking a function for each row scanned in table a.

​

Video demo

​

Known limitations

​

Limitations on use of UDFs

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

​

Limitations on UDF creation

• OUT and INOUT argument modes.
• RECORD input arguments.

​

Limitations on expressions allowed within UDFs

• CTEs (common table expressions).
• References to other user-defined functions.
• DDL statements (e.g., CREATE TABLE, CREATE INDEX).

​

See also

• CREATE FUNCTION
• ALTER FUNCTION
• DROP FUNCTION
• SHOW CREATE
• SQL Statements
• Functions and Operators