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
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