Home Explore Blog CI



postgresql

9th chunk of `doc/src/sgml/xfunc.sgml`
03e0f142b09c65d7dd212a6eb6faf7c2973b96f8fd0848270000000100000fa1
 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 &mdash; 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

Title: SQL Procedures with Output Parameters and Functions with Variable Numbers of Arguments
Summary
This section delves into SQL procedures and functions, focusing on output parameters and variable numbers of arguments. For procedures, output parameters must be included in the CALL command's argument list, often represented by NULL. When calling procedures from PL/pgSQL, variables are used to receive the output. Additionally, SQL functions can accept variable numbers of arguments of the same data type, passed as an array. The last parameter is marked as VARIADIC and declared as an array type. While passing an array directly isn't possible, variadic functions effectively gather the arguments into a one-dimensional array.