CALL statement [YSQL]

Synopsis

Use the CALL statement to execute a stored procedure.

Syntax

{{%ebnf%}} call_procedure, actual_arg, formal_arg {{%/ebnf%}}

{{< tip title="The syntax and semantics of 'actual_arg' are the same for function invocation as for 'CALL'." >}} The syntax and semantics of the subprogram_arg rule (for example how to use the named parameter invocation style to avoid providing actual arguments for defaulted parameters) are the same for invoking a function as for CALL. A function cannot be invoked with the CALL statement. Rather, it's invoked as (part of) an expression in DML statements like SELECT or in PL/pgSQL source code. {{< /tip >}}

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.