CREATE PROCEDURE

On this page Carat arrow pointing down

New in v23.2: The CREATE PROCEDURE statement defines a stored procedure.

Required privileges

  • To define a procedure, a user must have CREATE privilege on the schema of the procedure.
  • To define 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.
  • 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

CREATE OR REPLACE PROCEDURE routine_create_name ( routine_param , ) AS routine_body_str LANGUAGE SQL PLPGSQL

Parameters

Parameter Description
routine_create_name The name of the procedure.
routine_param A comma-separated list of procedure parameters.
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:

icon/buttons/copy
CREATE TYPE comp AS (x INT, y STRING);

Create the procedure, declaring the comp variable you created:

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

Create a stored procedure that uses conditional logic

The following example uses PL/pgSQL conditional statements:

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

icon/buttons/copy
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;
icon/buttons/copy
CALL arr_var();
NOTICE: 1: 1
NOTICE: 2: 2
NOTICE: 3: 3
NOTICE: 4: 4
NOTICE: 5: 5

See also


Yes No
On this page

Yes No