Try a preview of AI-powered search below!

Synopsis

Use the CALL statement to execute a stored procedure.

Syntax

• Grammar
• Diagram

call_procedure ::= CALL subprogram_name ( [ actual_arg [ , ... ] ] )

actual_arg ::= [ formal_arg => ] expression

formal_arg ::= name

Semantics

CALL executes a stored procedure. If the procedure has any output parameters, then a result row will be returned, containing the values of those parameters.

The caller must have both the usage privilege on the schema in which the to-be-called procedure exists and the execute privilege on it. If the caller lacks the required usage privilege, then it causes this error:

42501: permission denied for schema %"

If the caller has the required usage privilege but lacks the required execute privilege, then it causes this error:

42501: permission denied for procedure %

Notes

If CALL is executed in a transaction block, then it cannot execute transaction control statements. The attempt causes this runtime error:

2D000: invalid transaction termination

Transaction control statements are allowed when CALL is invoked with autocommit set to on—in which case the procedure executes in its own transaction.

Simple example

Create a simple procedure

set client_min_messages = warning;
drop procedure if exists p(text, int) cascade;
create procedure p(
  caption in text default 'Caption',
  int_val in int default 17)
  language plpgsql
as $body$
begin
  raise info 'Result: %: %', caption, int_val::text;
end;
$body$;

Invoke it using the simple syntax:

call p('Forty-two', 42);

This is the result:

INFO:  Result: Forty-two: 42

Omit the second defaulted parameter:

call p('"int_val" default is');

This is the result:

INFO:  Result: "int_val" default is: 17

Omit the both defaulted parameters:

call p();

This is the result:

INFO:  Result: Caption: 17

Invoke it by using the names of the formal parameters.

call p(caption => 'Forty-two', int_val=>42);

This is the result:

INFO:  Result: Forty-two: 42

Invoke it by just the named second parameter.

call p(int_val=>99);

This is the result:

INFO:  Result: Caption: 99

Provoke an error by using just the second unnamed parameter.

call p(99);

It causes this error:

42883: procedure p(integer) does not exist

In this case, this generic hint:

You might need to add explicit type casts.

isn't helpful.

Example with 'inout' arguments

Create a procedure with INOUT arguments.

drop procedure if exists x(int, int, int, int, int);
create procedure x(
  a inout int,
  b inout int,
  c inout int,
  d inout int,
  e inout int)
  language plpgsql
as $body$
begin
  a := a + 1;
  b := b + 2;
  c := c + 3;
  d := d + 4;
  e := e + 5;
end;
$body$;

Notice that this is rather strange. The ordinary meaning of an INOUT parameter is that its value will be changed by the invocation so that the caller sees different values after the call. Normally, a procedure designed and written to be called from another procedure or a DO block with actual arguments that are declared as variables. But when such a procedure is called using a top-level CALL statement, it returns the results in the same way that a SELECT statement does. Try this:

call x(10, 20, 30, 40, 50);

This is the result:

 a  | b  | c  | d  | e
----+----+----+----+----
 11 | 22 | 33 | 44 | 55

Here's how to invoke procedure x() in PLpgSQL:

do $body$
declare
  a_var int not null := 10;
  b_var int not null := 20;
  c_var int not null := 30;
  d_var int not null := 40;
  e_var int not null := 50;
begin
  call x(a_var, b_var, c_var, d_var, e_var);
  raise info 'Result: %, %, %, %, %', a_var, b_var, c_var, d_var, e_var;
end;
$body$;

This is the result:

INFO:  Result: 11, 22, 33, 44, 55

You cannot create a procedure with OUT formal arguments. The attempt causes the error

0A000: procedures cannot have OUT arguments

This is tracked by Github Issue #12348.