CREATE FUNCTION

On this page Carat arrow pointing down

The CREATE FUNCTION statement creates a user-defined function.

Note:

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 CREATE privilege on the schema of the function.
  • To define a function with a user-defined 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 OR REPLACE FUNCTION routine_create_name ( routine_param , ) RETURNS SETOF routine_return_type AS routine_body_str LANGUAGE SQL PLPGSQL CALLED RETURNS NULL ON NULL INPUT STRICT IMMUTABLE STABLE VOLATILE EXTERNAL SECURITY DEFINER INVOKER NOT LEAKPROOF

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.

Example of a simple function

The following statement creates a function to compute the square of integers:

icon/buttons/copy
CREATE OR REPLACE FUNCTION sq(a INT) RETURNS INT AS 'SELECT a*a' LANGUAGE SQL;

The following statement invokes the sq function:

icon/buttons/copy
SELECT sq(2);
  sq
-----
  4
(1 row)

Examples of functions that reference tables

Setup

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr sample dataset preloaded:

icon/buttons/copy
$ cockroach demo

Create a function that references a table

The following statement defines a function that returns the total number of MovR application users.

icon/buttons/copy
CREATE OR REPLACE FUNCTION num_users() RETURNS INT AS 'SELECT count(*) FROM users' LANGUAGE SQL;
icon/buttons/copy
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.

icon/buttons/copy
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%"}
icon/buttons/copy
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.

icon/buttons/copy
CREATE OR REPLACE FUNCTION total_euro_revenue() RETURNS DECIMAL LANGUAGE SQL AS $$
  SELECT SUM(revenue) FROM rides WHERE city IN ('paris', 'rome', 'amsterdam')
$$;
icon/buttons/copy
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.

icon/buttons/copy
CREATE OR REPLACE FUNCTION available_vehicles() RETURNS SETOF vehicles LANGUAGE SQL AS $$
  SELECT * FROM vehicles WHERE status = 'available'
$$;
icon/buttons/copy
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.

icon/buttons/copy
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)
  )
$$;
icon/buttons/copy
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.

icon/buttons/copy
CREATE OR REPLACE FUNCTION double_triple(INOUT double INT, OUT triple INT) AS 
  $$
  BEGIN
    double := double * 2;
    triple := double * 3;
  END;
  $$ LANGUAGE PLpgSQL;
icon/buttons/copy
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:

icon/buttons/copy
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.

icon/buttons/copy
CREATE OR REPLACE FUNCTION f(input_value INT)
  RETURNS RECORD 
  AS $$
  BEGIN
      RETURN double_triple(input_value);
  END;
  $$ LANGUAGE PLpgSQL;
icon/buttons/copy
SELECT f(1);
    f
---------
  (2,6)

Create a function that uses a loop

The following user-defined function returns the nth integer in the Fibonacci sequence.

It uses the PL/pgSQL LOOP syntax to iterate through a simple calculation, and RAISE EXCEPTION to return an error message if the specified n is negative.

icon/buttons/copy
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;
icon/buttons/copy
SELECT fib(8);
  fib
-------
   21

See also


Yes No
On this page

Yes No