Home Explore Blog CI



postgresql

5th chunk of `doc/src/sgml/ref/create_function.sgml`
d32a609d0c4a871883069811226ddb3c440b48134350570a0000000100000fa5

       <firstterm>window function</firstterm> rather than a plain function.
       This is currently only useful for functions written in C.
       The <literal>WINDOW</literal> attribute cannot be changed when
       replacing an existing function definition.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>IMMUTABLE</literal></term>
     <term><literal>STABLE</literal></term>
     <term><literal>VOLATILE</literal></term>

     <listitem>
      <para>
       These attributes inform the query optimizer about the behavior
       of the function.  At most one choice
       can be specified.  If none of these appear,
       <literal>VOLATILE</literal> is the default assumption.
      </para>

      <para><literal>IMMUTABLE</literal> indicates that the function
       cannot modify the database and always
       returns the same result when given the same argument values; that
       is, it does not do database lookups or otherwise use information not
       directly present in its argument list.  If this option is given,
       any call of the function with all-constant arguments can be
       immediately replaced with the function value.
      </para>

      <para><literal>STABLE</literal> indicates that the function
       cannot modify the database,
       and that within a single table scan it will consistently
       return the same result for the same argument values, but that its
       result could change across SQL statements.  This is the appropriate
       selection for functions whose results depend on database lookups,
       parameter variables (such as the current time zone), etc.  (It is
       inappropriate for <literal>AFTER</literal> triggers that wish to
       query rows modified by the current command.)  Also note
       that the <function>current_timestamp</function> family of functions qualify
       as stable, since their values do not change within a transaction.
      </para>

      <para><literal>VOLATILE</literal> indicates that the function value can
       change even within a single table scan, so no optimizations can be
       made.  Relatively few database functions are volatile in this sense;
       some examples are <literal>random()</literal>, <literal>currval()</literal>,
       <literal>timeofday()</literal>.  But note that any function that has
       side-effects must be classified volatile, even if its result is quite
       predictable, to prevent calls from being optimized away; an example is
       <literal>setval()</literal>.
      </para>

      <para>
       For additional details see <xref linkend="xfunc-volatility"/>.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>LEAKPROOF</literal></term>
     <listitem>
      <para>
       <literal>LEAKPROOF</literal> indicates that the function has no side
       effects.  It reveals no information about its arguments other than by
       its return value.  For example, a function which throws an error message
       for some argument values but not others, or which includes the argument
       values in any error message, is not leakproof.  This affects how the
       system executes queries against views created with the
       <literal>security_barrier</literal> option or tables with row level
       security enabled.  The system will enforce conditions from security
       policies and security barrier views before any user-supplied conditions
       from the query itself that contain non-leakproof functions, in order to
       prevent the inadvertent exposure of data.  Functions and operators
       marked as leakproof are assumed to be trustworthy, and may be executed
       before conditions from security policies and security barrier views.
       In addition, functions which do not take arguments or which are not
       passed any arguments from the security barrier view or table do not have
       to be marked as leakproof to be executed before security

Title: CREATE FUNCTION: Volatility and Leakproof Attributes
Summary
Explanation of function volatility attributes (IMMUTABLE, STABLE, VOLATILE) for query optimization. IMMUTABLE functions always return the same result for the same inputs and cannot modify the database. STABLE functions cannot modify the database and return consistent results within a table scan. VOLATILE functions can change values even within a single table scan. Also explains the LEAKPROOF attribute, which indicates that a function has no side effects and reveals no information about its arguments other than its return value. LEAKPROOF functions are trusted and executed before security policies.