Search code examples
postgresqlfunctionplpgsqlprocedure

PL/pgSQL: PERFORM vs CALL

While fiddling with the performance impact of calling a procedure from another procedure vs. repeating the code in Postgres 13, I found that you can invoke other procedures by using either CALL or PERFORM.

I googled for differences but the only similar thing I found was

Which addresses PERFORM vs. EXECUTE and not PERFORM vs. CALL.

Does anyone know what the difference is? Which one should I use (when calling a procedure from a PL/pgSQL procedure)?

Solution

  • CALL is an SQL command to execute a PROCEDURE and was added with Postgres 11, when SQL procedures were added. Example:

    CALL my_procedure('arg1');

    SELECT is the plain SQL command to execute a FUNCTION. Example:

    SELECT my_function('arg1');

    PERFORM is a PL/pgSQL command to replace the SELECT keyword (in an otherwise plain SQL SELECT command) and discard any result(s). Example:

    ...
BEGIN
   PERFORM my_function('arg1');
END
...

    EXECUTE is a PL/pgSQL command to execute a (dynamically generated) SQL string. Any complete SQL command is allowed, not just SELECT. Example:

    ...
BEGIN
   EXECUTE my_string_variable
   USING arg1;
END
...

    The SQL string can contain CALL or SELECT or any other SQL command. But parameter symbols referencing the USING clause are only allowed as data elements in the DML commands SELECT, INSERT, UPDATE, DELETE, and MERGE (these symbols cannot be used in place of table names, etc.).

    Results are discarded automatically unless an INTO clause is added.

    You cannot CALL a function.
    You cannot SELECT or PERFORM a procedure.
    You cannot PERFORM an EXECUTE, or EXECUTE a PERFORM, neither being an SQL command.

    db<>fiddle here

    Related: