The CREATE FUNCTION statement creates a user-defined function.
The CREATE FUNCTION statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Required privileges
- To define a function, a user must have
CREATEprivilege on the schema of the function. - To define a function with a user-defined type, a user must have
USAGEprivilege on the user-defined type. - To resolve a function, a user must have at least the
USAGEprivilege on the schema of the function. - To call a function, a user must have
EXECUTEprivilege on the function. - At function definition and execution time, a user must have privileges on all the objects referenced in the function body. Privileges on referenced objects can be revoked and later function calls can fail due to lack of permission.
If you grant EXECUTE privilege as a default privilege at the database level, newly created functions inherit that privilege from the database.
Synopsis
Parameters
| Parameter | Description |
|---|---|
func_create_name |
The name of the function. |
func_arg |
A function argument. |
func_arg_type |
The type returned by the function. |
opt_routine_body |
The body of the function. For allowed contents, see User-Defined Functions: Overview. |
Example of a simple function
The following statement creates a function to compute the square of integers:
> CREATE OR REPLACE FUNCTION sq(a INT) RETURNS INT AS 'SELECT a*a' LANGUAGE SQL;
The following statement invokes the sq function:
> SELECT sq(2);
sq
-----
4
(1 row)
Examples of functions that reference tables
Setup
The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.
To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:
$ cockroach demo
Create a function that references a table
The following statement defines a function that returns the total number of MovR application users.
> CREATE OR REPLACE FUNCTION num_users() RETURNS INT AS 'SELECT count(*) from users' LANGUAGE SQL;
> SELECT num_users();
num_users
-------------
50
(1 row)
Create a function that uses a WHERE clause
The following statement defines a function that returns the total revenue for rides taken in European cities.
> CREATE OR REPLACE FUNCTION total_euro_revenue() RETURNS DECIMAL LANGUAGE SQL AS $$
SELECT SUM(revenue) FROM rides WHERE city IN ('paris', 'rome', 'amsterdam')
$$;
> SELECT total_euro_revenue();
total_euro_revenue
----------------------
8468.00
