Home • Explore • Blog • CI
☰



postgresql
21th chunk of `doc/src/sgml/xfunc.sgml`
3f4213637bdadc0e97c444483beeff5c13fb097e8d93ebe40000000100000fa7
   each query they execute.
   </para>

   <note>
    <para>
     Functions written in C can manage snapshots however they want, but it's
     usually a good idea to make C functions work this way too.
    </para>
   </note>

   <para>
    Because of this snapshotting behavior,
    a function containing only <command>SELECT</command> commands can safely be
    marked <literal>STABLE</literal>, even if it selects from tables that might be
    undergoing modifications by concurrent queries.
    <productname>PostgreSQL</productname> will execute all commands of a
    <literal>STABLE</literal> function using the snapshot established for the
    calling query, and so it will see a fixed view of the database throughout
    that query.
   </para>

   <para>
    The same snapshotting behavior is used for <command>SELECT</command> commands
    within <literal>IMMUTABLE</literal> functions.  It is generally unwise to select
    from database tables within an <literal>IMMUTABLE</literal> function at all,
    since the immutability will be broken if the table contents ever change.
    However, <productname>PostgreSQL</productname> does not enforce that you
    do not do that.
   </para>

   <para>
    A common error is to label a function <literal>IMMUTABLE</literal> when its
    results depend on a configuration parameter.  For example, a function
    that manipulates timestamps might well have results that depend on the
    <xref linkend="guc-timezone"/> setting.  For safety, such functions should
    be labeled <literal>STABLE</literal> instead.
   </para>

   <note>
    <para>
     <productname>PostgreSQL</productname> requires that <literal>STABLE</literal>
     and <literal>IMMUTABLE</literal> functions contain no SQL commands other
     than <command>SELECT</command> to prevent data modification.
     (This is not a completely bulletproof test, since such functions could
     still call <literal>VOLATILE</literal> functions that modify the database.
     If you do that, you will find that the <literal>STABLE</literal> or
     <literal>IMMUTABLE</literal> function does not notice the database changes
     applied by the called function, since they are hidden from its snapshot.)
    </para>
   </note>
  </sect1>

  <sect1 id="xfunc-pl">
   <title>Procedural Language Functions</title>

   <para>
    <productname>PostgreSQL</productname> allows user-defined functions
    to be written in other languages besides SQL and C.  These other
    languages are generically called <firstterm>procedural
    languages</firstterm> (<acronym>PL</acronym>s).
    Procedural languages aren't built into the
    <productname>PostgreSQL</productname> server; they are offered
    by loadable modules.
    See <xref linkend="xplang"/> and following chapters for more
    information.
   </para>
  </sect1>

  <sect1 id="xfunc-internal">
   <title>Internal Functions</title>

   <indexterm zone="xfunc-internal"><primary>function</primary><secondary>internal</secondary></indexterm>

   <para>
    Internal functions are functions written in C that have been statically
    linked into the <productname>PostgreSQL</productname> server.
    The <quote>body</quote> of the function definition
    specifies the C-language name of the function, which need not be the
    same as the name being declared for SQL use.
    (For reasons of backward compatibility, an empty body
    is accepted as meaning that the C-language function name is the
    same as the SQL name.)
   </para>

   <para>
    Normally, all internal functions present in the
    server are declared during the initialization of the database cluster
    (see <xref linkend="creating-cluster"/>),
    but a user could use <command>CREATE FUNCTION</command>
    to create additional alias names for an internal function.
    Internal functions are declared in <command>CREATE FUNCTION</command>
    with language name <literal>internal</literal>.  For instance, to
    create an alias for the <function>sqrt</function> function:
Title: Snapshotting, Immutable Functions, and Procedural Languages
Summary
This section discusses snapshotting behavior within IMMUTABLE functions, advising against selecting from tables within them due to potential immutability violations. It notes that PostgreSQL doesn't enforce this restriction. A common error is labeling functions IMMUTABLE when their results depend on configuration parameters, suggesting STABLE as a safer alternative. Stable and IMMUTABLE functions should only contain SELECT commands to prevent data modification. The section then introduces procedural languages (PLs) as a means to write user-defined functions in languages other than SQL and C, offered via loadable modules, followed by the introduction of internal functions, which are functions written in C that are statically linked into the PostgreSQL server. These functions can be aliased via CREATE FUNCTION.