CREATE PROCEDURE - CockroachDB

The CREATE PROCEDURE defines a .

Required privileges

To define a procedure, a user must have on the schema of the procedure.
To define a procedure with a , a user must have on the user-defined type.
To resolve a procedure, a user must have at least the on the schema of the procedure.
To , a user must have on the procedure.
At procedure definition and execution time, a user must have privileges on all the objects referenced in the procedure body. Privileges on referenced objects can be revoked and later procedure calls can fail due to lack of permission.

If you grant EXECUTE privilege as a default privilege at the database level, newly created procedures inherit that privilege from the database.

Synopsis

Parameters

Parameter	Description
`routine\_create\_name`	The name of the procedure.
`routine\_param`	A comma-separated list of procedure parameters, specifying the mode, name, and type.
`routine\_body\_str`	The body of the procedure. For allowed contents, see .

Examples

The following are examples of basic stored procedures. For a more detailed example of a stored procedure, see .

Create a stored procedure that uses a composite-type variable

Create a :

CREATE TYPE comp AS (x INT, y STRING);

Create the procedure, declaring the comp variable you created:

CREATE OR REPLACE PROCEDURE proc() LANGUAGE PLpgSQL AS $$
  DECLARE
    v comp := ROW(1, 'foo');
  BEGIN
    RAISE NOTICE '%', v;
  END
  $$;

CALL proc();

NOTICE: (1,foo)
CALL

Create a stored procedure that uses `OUT` and `INOUT` parameters

The following example 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.

CREATE OR REPLACE PROCEDURE double_triple(INOUT double INT, OUT triple INT) AS
  $$
  BEGIN
    double := double * 2;
    triple := double * 3;
  END;
  $$ LANGUAGE PLpgSQL;

When calling a procedure, you need to supply placeholder values for any OUT parameters. A NULL value is commonly used. When calling a procedure from another routine, you should declare variables that will store the results of the OUT parameters.

CALL double_triple(1, NULL);

  double | triple
---------+---------
       2 |      6

Create a stored procedure that calls a procedure

The following example defines a procedure that calls the double_triple example procedure. The triple_result variable is assigned the result of the OUT parameter, while the double_input variable both provides the input and stores the result of the INOUT parameter.

A procedure with OUT parameters can only be .

CREATE OR REPLACE PROCEDURE p(double_input INT) AS
  $$
  DECLARE
    triple_result INT;
  BEGIN
    CALL double_triple(double_input, triple_result);
    RAISE NOTICE 'Doubled value: %', double_input;
    RAISE NOTICE 'Tripled value: %', triple_result;
  END
  $$ LANGUAGE PLpgSQL;

CALL p(1);

NOTICE: Doubled value: 2
NOTICE: Tripled value: 6
CALL

Create a stored procedure that uses conditional logic

The following example uses :

CREATE OR REPLACE PROCEDURE proc(a INT, b INT) AS
  $$
  DECLARE
    result INT;
  BEGIN
    IF a > b THEN
      RAISE NOTICE 'Condition met: a is greater than b';
    ELSE
      RAISE NOTICE 'Condition not met: a is not greater than b';
    END IF;
  END;
  $$ LANGUAGE PLpgSQL;

CALL proc(1, 2);

NOTICE: Condition not met: a is not greater than b
CALL

Create a stored procedure that uses a `WHILE` loop

The following example uses :

CREATE OR REPLACE PROCEDURE arr_var() AS
  $$
  DECLARE
    x INT[] := ARRAY[1, 2, 3, 4, 5];
    n INT;
    i INT := 1;
  BEGIN
    n := array_length(x, 1);
    WHILE i <= n LOOP
      RAISE NOTICE '%: %', i, x[i];
      i := i + 1;
    END LOOP;
  END
  $$ LANGUAGE PLpgSQL;

CALL arr_var();

NOTICE: 1: 1
NOTICE: 2: 2
NOTICE: 3: 3
NOTICE: 4: 4
NOTICE: 5: 5

​Required privileges

​Synopsis

​Parameters

​Examples

​Create a stored procedure that uses a composite-type variable

​Create a stored procedure that uses OUT and INOUT parameters

​Create a stored procedure that calls a procedure

​Create a stored procedure that uses conditional logic

​Create a stored procedure that uses a WHILE loop

​See also

Required privileges

Synopsis

Parameters

Examples

Create a stored procedure that uses a composite-type variable

Create a stored procedure that uses `OUT` and `INOUT` parameters

Create a stored procedure that calls a procedure

Create a stored procedure that uses conditional logic

Create a stored procedure that uses a `WHILE` loop

See also