CREATE PROCEDURE

The CREATE PROCEDURE statement defines a stored procedure.

Required privileges

To create a procedure, a user must have CREATE privilege on the schema of the procedure. The user must also have privileges on all the objects referenced in the procedure body.
To create a procedure with a user-defined type, a user must have USAGE privilege on the user-defined type.
To resolve a procedure, a user must have at least the USAGE privilege on the schema of the procedure.
To call a procedure, a user must have EXECUTE privilege on the procedure. By default, the user must also have privileges on all the objects referenced in the procedure body. However, a SECURITY DEFINER procedure executes with the privileges of the user that owns the procedure, not the user that calls it. A SECURITY INVOKER procedure executes with the privileges of the user that calls the procedure, thus matching the default behavior.

For an example of SECURITY DEFINER, refer to Create a SECURITY DEFINER function.

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 Stored Procedures.

Examples

The following are examples of basic stored procedures. For a more detailed example of a stored procedure, see Create a stored procedure using PL/pgSQL.

Create a stored procedure that uses a composite-type variable

Create a composite variable:

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 called from a PL/pgSQL routine.

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 PL/pgSQL conditional statements:

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 PL/pgSQL loop statements:

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

Create a `LANGUAGE SQL` procedure that contains DDL

The following LANGUAGE SQL procedure creates a table when called. A LANGUAGE SQL procedure body can include DDL and DCL statements without late binding. Because a LANGUAGE SQL procedure body is early-bound, its statements cannot reference an object created earlier in the body.

CREATE OR REPLACE PROCEDURE create_bank() LANGUAGE SQL AS $$
  CREATE TABLE IF NOT EXISTS bank (id INT PRIMARY KEY, balance DECIMAL)
$$;

CALL create_bank();

Create a late-bound PL/pgSQL procedure that contains DDL

A PL/pgSQL procedure body that contains DDL requires late binding. Enable the sql.procedures.plpgsql.late_binding.enabled cluster setting:

SET CLUSTER SETTING sql.procedures.plpgsql.late_binding.enabled = true;

Create a role for the procedure to grant privileges to:

CREATE ROLE app_user;

The following procedure creates a schema and a table, grants privileges on the table, and inserts a row. Because the procedure uses late binding, the GRANT and INSERT statements can reference the table created earlier in the procedure body.

CREATE OR REPLACE PROCEDURE provision_app() LANGUAGE PLpgSQL AS $$
BEGIN
  CREATE SCHEMA IF NOT EXISTS app;
  CREATE TABLE IF NOT EXISTS app.accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    balance DECIMAL
  );
  GRANT SELECT, INSERT ON TABLE app.accounts TO app_user;
  INSERT INTO app.accounts (balance) VALUES (0);
END
$$;

CALL provision_app();

NOTICE: waiting for job(s) to complete: 1193313222013091841
If the statement is canceled, jobs will continue in the background.
CALL

SHOW GRANTS ON TABLE app.accounts FOR app_user;

  database_name | schema_name | table_name | grantee  | privilege_type | is_grantable
----------------+-------------+------------+----------+----------------+---------------
  defaultdb     | app         | accounts   | app_user | INSERT         |      f
  defaultdb     | app         | accounts   | app_user | SELECT         |      f
(2 rows)

Statements

Syntax

Data Types

Spatial Data

Constraints

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

Create a `LANGUAGE SQL` procedure that contains DDL

Create a late-bound PL/pgSQL procedure that contains DDL

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

​Create a LANGUAGE SQL procedure that contains DDL

​Create a late-bound PL/pgSQL procedure that contains DDL

​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

Create a `LANGUAGE SQL` procedure that contains DDL

Create a late-bound PL/pgSQL procedure that contains DDL

See also