xfunc.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning "anyarray" or "anyelement" must have at least one argument of either type.
</screen>
    </para>

    <para>
     Polymorphism can be used with functions that have output arguments.
     For example:
<screen>
CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)
</screen>
    </para>
   </sect2>
  </sect1>

  <sect1 id="xfunc-overload">
   <title>Function Overloading</title>

   <indexterm zone="xfunc-overload">
    <primary>overloading</primary>
    <secondary>functions</secondary>
   </indexterm>

   <para>
    More than one function may be defined with the same SQL name, so long
    as the arguments they take are different.  In other words,
    function names can be <firstterm>overloaded</firstterm>.  When a
    query is executed, the server will determine which function to
    call from the data types and the number of the provided arguments.
    Overloading can also be used to simulate functions with a variable
    number of arguments, up to a finite maximum number.
   </para>

   <para>
    When creating a family of overloaded functions, one should be
    careful not to create ambiguities.  For instance, given the
    functions
<programlisting>
CREATE FUNCTION test(int, real) RETURNS ...
CREATE FUNCTION test(smallint, double precision) RETURNS ...
</programlisting>
    it is not immediately clear which function would be called with
    some trivial input like <literal>test(1, 1.5)</literal>.  The
    currently implemented resolution rules are described in
    <xref linkend="typeconv">, but it is unwise to design a system that subtly
    relies on this behavior.
   </para>

   <para>
    A function that takes a single argument of a composite type should
    generally not have the same name as any attribute (field) of that type.
    Recall that <literal>attribute(table)</literal> is considered equivalent
    to <literal>table.attribute</literal>.  In the case that there is an
    ambiguity between a function on a composite type and an attribute of
    the composite type, the attribute will always be used.  It is possible
    to override that choice by schema-qualifying the function name
    (that is, <literal>schema.func(table)</literal>) but it's better to
    avoid the problem by not choosing conflicting names.
   </para>

   <para>
    When overloading C-language functions, there is an additional
    constraint: The C name of each function in the family of
    overloaded functions must be different from the C names of all
    other functions, either internal or dynamically loaded.  If this
    rule is violated, the behavior is not portable.  You might get a
    run-time linker error, or one of the functions will get called
    (usually the internal one).  The alternative form of the
    <literal>AS</> clause for the SQL <command>CREATE
    FUNCTION</command> command decouples the SQL function name from
    the function name in the C source code.  For instance,
<programlisting>
CREATE FUNCTION test(int) RETURNS int
    AS '<replaceable>filename</>', 'test_1arg'
    LANGUAGE C;
CREATE FUNCTION test(int, int) RETURNS int
    AS '<replaceable>filename</>', 'test_2arg'
    LANGUAGE C;
</programlisting>
    The names of the C functions here reflect one of many possible conventions.
   </para>
  </sect1>

  <sect1 id="xfunc-volatility">
   <title>Function Volatility Categories</title>

   <indexterm zone="xfunc-volatility">
    <primary>volatility</primary>
    <secondary>functions</secondary>
   </indexterm>
   <indexterm zone="xfunc-volatility">
    <primary>VOLATILE</primary>
   </indexterm>
   <indexterm zone="xfunc-volatility">
    <primary>STABLE</primary>
   </indexterm>
   <indexterm zone="xfunc-volatility">
    <primary>IMMUTABLE</primary>
   </indexterm>

   <para>
    Every function has a <firstterm>volatility</> classification, with
    the possibilities being <literal>VOLATILE</>, <literal>STABLE</>, or
    <literal>IMMUTABLE</>.  <literal>VOLATILE</> is the default if the
    <xref linkend="sql-createfunction" endterm="sql-createfunction-title">
    command does not specify a category.  The volatility category is a
    promise to the optimizer about the behavior of the function:

   <itemizedlist>
    <listitem>
     <para>
      A <literal>VOLATILE</> function can do anything, including modifying
      the database.  It can return different results on successive calls with
      the same arguments.  The optimizer makes no assumptions about the
      behavior of such functions.  A query using a volatile function will
      re-evaluate the function at every row where its value is needed.
     </para>
    </listitem>
    <listitem>
     <para>
      A <literal>STABLE</> function cannot modify the database and is
      guaranteed to return the same results given the same arguments
      for all rows within a single statement. This category allows the
      optimizer to optimize multiple calls of the function to a single
      call. In particular, it is safe to use an expression containing
      such a function in an index scan condition. (Since an index scan
      will evaluate the comparison value only once, not once at each
      row, it is not valid to use a <literal>VOLATILE</> function in an
      index scan condition.)
     </para>
    </listitem>
    <listitem>
     <para>
      An <literal>IMMUTABLE</> function cannot modify the database and is
      guaranteed to return the same results given the same arguments forever.
      This category allows the optimizer to pre-evaluate the function when
      a query calls it with constant arguments.  For example, a query like
      <literal>SELECT ... WHERE x = 2 + 2</> can be simplified on sight to
      <literal>SELECT ... WHERE x = 4</>, because the function underlying
      the integer addition operator is marked <literal>IMMUTABLE</>.
     </para>
    </listitem>
   </itemizedlist>
   </para>

   <para>
    For best optimization results, you should label your functions with the
    strictest volatility category that is valid for them.
   </para>

   <para>
    Any function with side-effects <emphasis>must</> be labeled
    <literal>VOLATILE</>, so that calls to it cannot be optimized away.
    Even a function with no side-effects needs to be labeled
    <literal>VOLATILE</> if its value can change within a single query;
    some examples are <literal>random()</>, <literal>currval()</>,
    <literal>timeofday()</>.
   </para>

   <para>
    There is relatively little difference between <literal>STABLE</> and
    <literal>IMMUTABLE</> categories when considering simple interactive
    queries that are planned and immediately executed: it doesn't matter
    a lot whether a function is executed once during planning or once during
    query execution startup.  But there is a big difference if the plan is
    saved and reused later.  Labeling a function <literal>IMMUTABLE</> when
    it really isn't may allow it to be prematurely folded to a constant during
    planning, resulting in a stale value being re-used during subsequent uses
    of the plan.  This is a hazard when using prepared statements or when
    using function languages that cache plans (such as
    <application>PL/pgSQL</>).
   </para>

   <para>
    Because of the snapshotting behavior of MVCC (see <xref linkend="mvcc">)
    a function containing only <command>SELECT</> commands can safely be
    marked <literal>STABLE</>, even if it selects from tables that might be
    undergoing modifications by concurrent queries.
    <productname>PostgreSQL</productname> will execute a <literal>STABLE</>
    function using the snapshot established for the calling query, and so it
    will see a fixed view of the database throughout that query.
    Also note
    that the <function>current_timestamp</> family of functions qualify
    as stable, since their values do not change within a transaction.
   </para>

   <para>
    The same snapshotting behavior is used for <command>SELECT</> commands
    within <literal>IMMUTABLE</> functions.  It is generally unwise to select
    from database tables within an <literal>IMMUTABLE</> 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</> 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</> instead.
   </para>

   <note>
    <para>
     Before <productname>PostgreSQL</productname> release 8.0, the requirement
     that <literal>STABLE</> and <literal>IMMUTABLE</> functions cannot modify
     the database was not enforced by the system.  Release 8.0 enforces it
     by requiring SQL functions and procedural language functions of these
     categories to contain no SQL commands other than <command>SELECT</>.
     (This is not a completely bulletproof test, since such functions could
     still call <literal>VOLATILE</> functions that modify the database.
     If you do that, you will find that the <literal>STABLE</> or
     <literal>IMMUTABLE</> function does not notice the database changes
     applied by the called function.)
    </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</>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</><secondary>internal</></>

   <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 backwards 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 (<command>initdb</command>),
    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:
<programlisting>
CREATE FUNCTION square_root(double precision) RETURNS double precision
    AS 'dsqrt'
    LANGUAGE internal
    STRICT;
</programlisting>
    (Most internal functions expect to be declared <quote>strict</quote>.)
   </para>

   <note>
    <para>
     Not all <quote>predefined</quote> functions are
     <quote>internal</quote> in the above sense.  Some predefined
     functions are written in SQL.
    </para>
   </note>
  </sect1>

  <sect1 id="xfunc-c">
   <title>C-Language Functions</title>

   <indexterm zone="xfunc-c">
    <primary>function</primary>
    <secondary>user-defined</secondary>
    <tertiary>in C</tertiary>
   </indexterm>

   <para>
    User-defined functions can be written in C (or a language that can
    be made compatible with C, such as C++).  Such functions are
    compiled into dynamically loadable objects (also called shared
    libraries) and are loaded by the server on demand.  The dynamic
    loading feature is what distinguishes <quote>C language</> functions
    from <quote>internal</> functions &mdash; the actual coding conventions
    are essentially the same for both.  (Hence, the standard internal
    function library is a rich source of coding examples for user-defined
    C functions.)
   </para>

   <para>
    Two different calling conventions are currently used for C functions.
    The newer <quote>version 1</quote> calling convention is indicated by writing
    a <literal>PG_FUNCTION_INFO_V1()</literal> macro call for the function,
    as illustrated below.  Lack of such a macro indicates an old-style
    (<quote>version 0</quote>) function.  The language name specified in <command>CREATE FUNCTION</command>
    is <literal>C</literal> in either case.  Old-style functions are now deprecated
    because of portability problems and lack of functionality, but they
    are still supported for compatibility reasons.
   </para>

  <sect2 id="xfunc-c-dynload">
   <title>Dynamic Loading</title>

   <indexterm zone="xfunc-c-dynload">
    <primary>dynamic loading</primary>
   </indexterm>

   <para>
    The first time a user-defined function in a particular
    loadable object file is called in a session,
    the dynamic loader loads that object file into memory so that the
    function can be called.  The <command>CREATE FUNCTION</command>
    for a user-defined C function must therefore specify two pieces of
    information for the function: the name of the loadable
    object file, and the C name (link symbol) of the specific function to call
    within that object file.  If the C name is not explicitly specified then
    it is assumed to be the same as the SQL function name.
   </para>

   <para>
    The following algorithm is used to locate the shared object file
    based on the name given in the <command>CREATE FUNCTION</command>
    command:

    <orderedlist>
     <listitem>
      <para>
       If the name is an absolute path, the given file is loaded.
      </para>
     </listitem>

     <listitem>
      <para>
       If the name starts with the string <literal>$libdir</literal>,
       that part is replaced by the <productname>PostgreSQL</> package
        library directory
       name, which is determined at build time.<indexterm><primary>$libdir</></>
      </para>
     </listitem>

     <listitem>
      <para>
       If the name does not contain a directory part, the file is
       searched for in the path specified by the configuration variable
       <xref linkend="guc-dynamic-library-path">.<indexterm><primary>dynamic_library_path</></>
      </para>
     </listitem>

     <listitem>
      <para>
       Otherwise (the file was not found in the path, or it contains a
       non-absolute directory part), the dynamic loader will try to
       take the name as given, which will most likely fail.  (It is
       unreliable to depend on the current working directory.)
      </para>
     </listitem>
    </orderedlist>

    If this sequence does not work, the platform-specific shared
    library file name extension (often <filename>.so</filename>) is
    appended to the given name and this sequence is tried again.  If