> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # CREATE FUNCTION The `CREATE FUNCTION` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) creates a [user-defined function](https://docs.cockroachlabs.com/docs/v24.2/user-defined-functions). > [!NOTE] > > The `CREATE FUNCTION` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes). ## Required privileges * To define a function, a user must have [`CREATE` privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges) on the schema of the function. * To define a function with a [user-defined type](https://docs.cockroachlabs.com/docs/v24.2/create-type), a user must have `USAGE` privilege on the user-defined type. * To resolve a function, a user must have at least the `USAGE` privilege on the schema of the function. * To call a function, a user must have `EXECUTE` privilege 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 ![create\_func syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/create_func.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=63b59c2e6e3ecf8b56848a7026720a47) ## Parameters | Parameter | Description | | - | - | | `routine_create_name` | The name of the function. | | `routine_param` | A comma-separated list of function parameters, specifying the mode, name, and type. | | `routine_return_type` | The type returned by the function. | | `routine_body_str` | The body of the function. For allowed contents, see [User-Defined Functions](https://docs.cockroachlabs.com/docs/v24.2/user-defined-functions#overview). | ## Example of a simple function The following statement creates a function to compute the square of integers: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION sq(a INT) RETURNS INT AS 'SELECT a*a' LANGUAGE SQL; ``` The following statement invokes the `sq` function: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT sq(2); ``` ``` sq ----- 4 (1 row) ``` ## Examples of functions that reference tables #### Setup To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/v24.2/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](https://docs.cockroachlabs.com/docs/v24.2/movr) sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` ### Create a function that references a table The following statement defines a function that returns the total number of MovR application users. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION num_users() RETURNS INT AS 'SELECT count(*) FROM users' LANGUAGE SQL; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT num_users(); ``` ``` num_users ------------- 50 (1 row) ``` ### Create a function that modifies a table The following statement defines a function that updates the `rules` value for a specified row in `promo_codes`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION update_code( code_name VARCHAR, new_rules JSONB ) RETURNS promo_codes AS $$ UPDATE promo_codes SET rules = new_rules WHERE code = code_name RETURNING *; $$ LANGUAGE SQL; ``` Given the `promo_codes` row: ``` code | description | creation_time | expiration_time | rules ---------------------------+----------------------------------------------------------------+---------------------+---------------------+----------------------------------------------- 0_building_it_remember | Door let Mrs manager buy model. Course rock training together. | 2019-01-09 03:04:05 | 2019-01-14 03:04:05 | {"type": "percent_discount", "value": "10%"} ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT update_code('0_building_it_remember', '{"type": "percent_discount", "value": "50%"}'); ``` ``` update_code ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ (0_building_it_remember,"Door let Mrs manager buy model. Course rock training together.","2019-01-09 03:04:05","2019-01-14 03:04:05","{""type"": ""percent_discount"", ""value"": ""50%""}") ``` ### 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. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION total_euro_revenue() RETURNS DECIMAL LANGUAGE SQL AS $$ SELECT SUM(revenue) FROM rides WHERE city IN ('paris', 'rome', 'amsterdam') $$; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT total_euro_revenue(); ``` ``` total_euro_revenue ---------------------- 8468.00 ``` ### Create a function that returns a set of results The following statement defines a function that returns information for all vehicles not in use. The `SETOF` clause specifies that the function should return each row as the query executes to completion. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION available_vehicles() RETURNS SETOF vehicles LANGUAGE SQL AS $$ SELECT * FROM vehicles WHERE status = 'available' $$; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT city,current_location,type FROM available_vehicles(); ``` ``` city | current_location | type ----------------+-----------------------------+------------- amsterdam | 4102 Stout Flat Apt. 11 | skateboard boston | 30226 Logan Branch Suite 76 | skateboard los angeles | 25730 Crystal Terrace | scooter paris | 9429 Joseph Neck Suite 52 | skateboard san francisco | 43325 Jeffrey Wall Suite 26 | scooter (5 rows) ``` ### Create a function that returns a `RECORD` type The following statement defines a function that returns the information for the user that most recently completed a ride. The information is returned as a record, which takes the structure of the row that is retrieved by the selection query. In the function subquery, the latest `end_time` timestamp is used to determine the most recently completed ride. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION last_rider() RETURNS RECORD LANGUAGE SQL AS $$ SELECT * FROM users WHERE id = ( SELECT rider_id FROM rides WHERE end_time = (SELECT max(end_time) FROM rides) ) $$; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT last_rider(); ``` ``` last_rider ---------------------------------------------------------------------------------------------------------- (70a3d70a-3d70-4400-8000-000000000016,seattle,"Mary Thomas","43322 Anthony Flats Suite 85",1141093639) (1 row) ``` ### Create a function that uses `OUT` and `INOUT` parameters The following statement uses a combination of `OUT` and `INOUT` parameters to modify a provided value and output the result. An `OUT` parameter returns a value, while an `INOUT` parameter passes an input value and returns a value. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION double_triple(INOUT double INT, OUT triple INT) AS $$ BEGIN double := double * 2; triple := double * 3; END; $$ LANGUAGE PLpgSQL; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT double_triple(1); ``` ``` double_triple ----------------- (2,6) ``` The `CREATE FUNCTION` statement does not need a `RETURN` statement because this is added implicitly for a function with `OUT` parameters: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW CREATE FUNCTION double_triple; ``` ``` function_name | create_statement ----------------+--------------------------------------------------------------------------- double_triple | CREATE FUNCTION public.double_triple(INOUT double INT8, OUT triple INT8) | RETURNS RECORD | VOLATILE | NOT LEAKPROOF | CALLED ON NULL INPUT | LANGUAGE plpgsql | AS $$ | BEGIN | double := double * 2; | triple := double * 3; | END; | $$ ``` ### Create a function that invokes a function The following statement defines a function that invokes the [`double_triple` example function](#create-a-function-that-uses-out-and-inout-parameters). ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE OR REPLACE FUNCTION f(input_value INT) RETURNS RECORD AS $$ BEGIN RETURN double_triple(input_value); END; $$ LANGUAGE PLpgSQL; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT f(1); ``` ``` f --------- (2,6) ``` ### Create a function that uses a loop The following [user-defined function](https://docs.cockroachlabs.com/docs/v24.2/user-defined-functions)user-defined function returns the `n`th integer in the Fibonacci sequence. It uses the [PL/pgSQL](https://docs.cockroachlabs.com/docs/v24.2/plpgsql) [`LOOP`](https://docs.cockroachlabs.com/docs/v24.2/plpgsql#write-loops) syntax to iterate through a simple calculation, and [`RAISE EXCEPTION`](https://docs.cockroachlabs.com/docs/v24.2/plpgsql#report-messages-and-handle-exceptions) to return an error message if the specified `n` is negative. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT fib(8); ``` ``` fib ------- 21 ``` ## See also * [User-Defined Functions](https://docs.cockroachlabs.com/docs/v24.2/user-defined-functions) * [`ALTER FUNCTION`](https://docs.cockroachlabs.com/docs/v24.2/alter-function) * [`DROP FUNCTION`](https://docs.cockroachlabs.com/docs/v24.2/drop-function) * [SQL Statements](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) * [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes)