Support for OUT parameters in procedures

Unlike for functions, OUT parameters for procedures are part of the
signature.  Therefore, they have to be listed in pg_proc.proargtypes
as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE.

Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com

9 files changed, 126 insertions, 25 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 0e580b157f5..3927b1030df 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5875,8 +5875,9 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       <para>
        An array with the data types of the function arguments.  This includes
        only input arguments (including <literal>INOUT</literal> and
-       <literal>VARIADIC</literal> arguments), and thus represents
-       the call signature of the function.
+       <literal>VARIADIC</literal> arguments), as well as
+       <literal>OUT</literal> parameters of procedures, and thus represents
+       the call signature of the function or procedure.
       </para></entry>
      </row>
 
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index c2bb3e32685..74b6b258780 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -479,6 +479,14 @@ $$ LANGUAGE plpgsql;
      </para>
 
      <para>
+      To call a function with <literal>OUT</literal> parameters, omit the
+      output parameter in the function call:
+<programlisting>
+SELECT sales_tax(100.00);
+</programlisting>
+     </para>
+
+     <para>
       Output parameters are most useful when returning multiple values.
       A trivial example is:
 
@@ -489,6 +497,11 @@ BEGIN
     prod := x * y;
 END;
 $$ LANGUAGE plpgsql;
+
+SELECT * FROM sum_n_product(2, 4);
+ sum | prod
+-----+------
+   6 |    8
 </programlisting>
 
       As discussed in <xref linkend="xfunc-output-parameters"/>, this
@@ -498,6 +511,31 @@ $$ LANGUAGE plpgsql;
      </para>
 
      <para>
+      This also works with procedures, for example:
+
+<programlisting>
+CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
+BEGIN
+    sum := x + y;
+    prod := x * y;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+      In a call to a procedure, all the parameters must be specified.  For
+      output parameters, <literal>NULL</literal> may be specified.
+<programlisting>
+CALL sum_n_product(2, 4, NULL, NULL);
+ sum | prod
+-----+------
+   6 |    8
+</programlisting>
+      Output parameters in procedures become more interesting in nested calls,
+      where they can be assigned to variables.  See <xref
+      linkend="plpgsql-statements-calling-procedure"/> for details.
+     </para>
+
+     <para>
       Another way to declare a <application>PL/pgSQL</application> function
       is with <literal>RETURNS TABLE</literal>, for example:
 
diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml
index c819c7bb4e3..38fd60128b7 100644
--- a/doc/src/sgml/ref/alter_extension.sgml
+++ b/doc/src/sgml/ref/alter_extension.sgml
@@ -212,11 +212,12 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
        argument: <literal>IN</literal>, <literal>OUT</literal>,
        <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
        If omitted, the default is <literal>IN</literal>.
-       Note that <command>ALTER EXTENSION</command> does not actually pay
-       any attention to <literal>OUT</literal> arguments, since only the input
-       arguments are needed to determine the function's identity.
-       So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
-       and <literal>VARIADIC</literal> arguments.
+       Note that <command>ALTER EXTENSION</command> does not actually pay any
+       attention to <literal>OUT</literal> arguments for functions and
+       aggregates (but not procedures), since only the input arguments are
+       needed to determine the function's identity.  So it is sufficient to
+       list the <literal>IN</literal>, <literal>INOUT</literal>, and
+       <literal>VARIADIC</literal> arguments for functions and aggregates.
       </para>
      </listitem>
     </varlistentry>
diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml
index bcf45c7a85f..5c176fb5d87 100644
--- a/doc/src/sgml/ref/alter_procedure.sgml
+++ b/doc/src/sgml/ref/alter_procedure.sgml
@@ -81,8 +81,9 @@ ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="para
 
     <listitem>
      <para>
-      The mode of an argument: <literal>IN</literal>  or <literal>VARIADIC</literal>.
-      If omitted, the default is <literal>IN</literal>.
+      The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
+      <literal>INOUT</literal>, or <literal>VARIADIC</literal>.  If omitted,
+      the default is <literal>IN</literal>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 6e8ced3eaf1..eda91b4e240 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -178,11 +178,12 @@ COMMENT ON
       argument: <literal>IN</literal>, <literal>OUT</literal>,
       <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
       If omitted, the default is <literal>IN</literal>.
-      Note that <command>COMMENT</command> does not actually pay
-      any attention to <literal>OUT</literal> arguments, since only the input
-      arguments are needed to determine the function's identity.
-      So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
-      and <literal>VARIADIC</literal> arguments.
+      Note that <command>COMMENT</command> does not actually pay any attention
+      to <literal>OUT</literal> arguments for functions and aggregates (but
+      not procedures), since only the input arguments are needed to determine
+      the function's identity.  So it is sufficient to list the
+      <literal>IN</literal>, <literal>INOUT</literal>, and
+      <literal>VARIADIC</literal> arguments for functions and aggregates.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml
index 36c307cadc7..e258eca5cee 100644
--- a/doc/src/sgml/ref/create_procedure.sgml
+++ b/doc/src/sgml/ref/create_procedure.sgml
@@ -97,11 +97,9 @@ CREATE [ OR REPLACE ] PROCEDURE
 
      <listitem>
       <para>
-       The mode of an argument: <literal>IN</literal>,
+       The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
        <literal>INOUT</literal>, or <literal>VARIADIC</literal>.  If omitted,
-       the default is <literal>IN</literal>.  (<literal>OUT</literal>
-       arguments are currently not supported for procedures.  Use
-       <literal>INOUT</literal> instead.)
+       the default is <literal>IN</literal>.
       </para>
      </listitem>
     </varlistentry>
diff --git a/doc/src/sgml/ref/drop_procedure.sgml b/doc/src/sgml/ref/drop_procedure.sgml
index 6da266ae2da..bf2c6ce1aaa 100644
--- a/doc/src/sgml/ref/drop_procedure.sgml
+++ b/doc/src/sgml/ref/drop_procedure.sgml
@@ -67,8 +67,9 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
 
     <listitem>
      <para>
-      The mode of an argument: <literal>IN</literal> or <literal>VARIADIC</literal>.
-      If omitted, the default is <literal>IN</literal>.
+      The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
+      <literal>INOUT</literal>, or <literal>VARIADIC</literal>.  If omitted,
+      the default is <literal>IN</literal>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/security_label.sgml b/doc/src/sgml/ref/security_label.sgml
index e9688cce214..9b87bcd5196 100644
--- a/doc/src/sgml/ref/security_label.sgml
+++ b/doc/src/sgml/ref/security_label.sgml
@@ -127,11 +127,12 @@ SECURITY LABEL [ FOR <replaceable class="parameter">provider</replaceable> ] ON
       argument: <literal>IN</literal>, <literal>OUT</literal>,
       <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
       If omitted, the default is <literal>IN</literal>.
-      Note that <command>SECURITY LABEL</command> does not actually
-      pay any attention to <literal>OUT</literal> arguments, since only the input
-      arguments are needed to determine the function's identity.
-      So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
-      and <literal>VARIADIC</literal> arguments.
+      Note that <command>SECURITY LABEL</command> does not actually pay any
+      attention to <literal>OUT</literal> arguments for functions and
+      aggregates (but not procedures), since only the input arguments are
+      needed to determine the function's identity.  So it is sufficient to
+      list the <literal>IN</literal>, <literal>INOUT</literal>, and
+      <literal>VARIADIC</literal> arguments for functions and aggregates.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 8c74c11d3b5..2863f7c2065 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -179,6 +179,24 @@ SELECT clean_emp();
 </screen>
     </para>
 
+    <para>
+     You can also write this as a procedure, thus avoiding the issue of the
+     return type.  For example:
+<screen>
+CREATE PROCEDURE clean_emp() AS '
+    DELETE FROM emp
+        WHERE salary &lt; 0;
+' LANGUAGE SQL;
+
+CALL clean_emp();
+</screen>
+     In simple cases like this, the difference between a function returning
+     <type>void</type> and a procedure is mostly stylistic.  However,
+     procedures offer additional functionality such as transaction control
+     that is not available in functions.  Also, procedures are SQL standard
+     whereas returning <type>void</type> is a PostgreSQL extension.
+    </para>
+
     <note>
      <para>
       The entire body of a SQL function is parsed before any of it is
@@ -716,6 +734,47 @@ DROP FUNCTION sum_n_product (int, int);
     </para>
    </sect2>
 
+   <sect2 id="xfunc-output-parameters-proc">
+    <title><acronym>SQL</acronym> Procedures with Output Parameters</title>
+
+    <indexterm>
+     <primary>procedures</primary>
+     <secondary>output parameter</secondary>
+    </indexterm>
+
+    <para>
+     Output parameters are also supported in procedures, but they work a bit
+     differently from functions.  Notably, output parameters
+     <emphasis>are</emphasis> included in the signature of a procedure and
+     must be specified in the procedure call.
+    </para>
+
+    <para>
+     For example, the bank account debiting routine from earlier could be
+     written like this:
+<programlisting>
+CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
+    UPDATE bank
+        SET balance = balance - debit
+        WHERE accountno = tp1.accountno
+    RETURNING balance;
+$$ LANGUAGE SQL;
+</programlisting>
+     To call this procedure, it is irrelevant what is passed as the argument
+     of the <literal>OUT</literal> parameter, so you could pass
+     <literal>NULL</literal>:
+<programlisting>
+CALL tp1(17, 100.0, NULL);
+</programlisting>
+    </para>
+
+    <para>
+     Procedures with output parameters are more useful in PL/pgSQL, where the
+     output parameters can be assigned to variables.  See <xref
+     linkend="plpgsql-statements-calling-procedure"/> for details.
+    </para>
+   </sect2>
+
    <sect2 id="xfunc-sql-variadic-functions">
     <title><acronym>SQL</acronym> Functions with Variable Numbers of Arguments</title>