FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);
</screen>
</para>
<para>
Parameters can be marked as <literal>IN</literal> (the default),
<literal>OUT</literal>, <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
An <literal>INOUT</literal>
parameter serves as both an input parameter (part of the calling
argument list) and an output parameter (part of the result record type).
<literal>VARIADIC</literal> parameters are input parameters, but are treated
specially as described below.
</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. In <command>CALL</command> commands,
output parameters must be included in the argument list.
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, an argument matching the <literal>OUT</literal>
parameter must be included. It's customary to write
<literal>NULL</literal>:
<programlisting>
CALL tp1(17, 100.0, NULL);
</programlisting>
If you write something else, it must be an expression that is implicitly
coercible to the declared type of the parameter, just as for input
parameters. Note however that such an expression will not be evaluated.
</para>
<para>
When calling a procedure from <application>PL/pgSQL</application>,
instead of writing <literal>NULL</literal> you must write a variable
that will receive the procedure's output. 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>
<indexterm>
<primary>function</primary>
<secondary>variadic</secondary>
</indexterm>
<indexterm>
<primary>variadic function</primary>
</indexterm>
<para>
<acronym>SQL</acronym> functions can be declared to accept
variable numbers of arguments, so long as all the <quote>optional</quote>
arguments are of the same data type. The optional arguments will be
passed to the function as an array. The function is declared by
marking the last parameter as <literal>VARIADIC</literal>; this parameter
must be declared as being of an array type. For example:
<screen>
CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
SELECT mleast(10, -1, 5, 4.4);
mleast
--------
-1
(1 row)
</screen>
Effectively, all the actual arguments at or beyond the
<literal>VARIADIC</literal> position are gathered up into a one-dimensional
array, as if you had written
<screen>
SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- doesn't work
</screen>
You can't actually write that, though — or at least, it will
not match this function definition. A parameter marked
<literal>VARIADIC</literal> matches one or more occurrences of its element
type, not of its own type.
</para>
<para>
Sometimes it is useful to be able to pass an already-constructed array
to a variadic function; this is particularly handy when one variadic
function wants to pass on its array parameter to another one. Also,
this is the only secure way to call a variadic function