create_function.sgml

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

     </listitem>
    </varlistentry>

   <varlistentry>
    <term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term>
    <term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term>

    <listitem>
     <para>
      <literal>SECURITY INVOKER</literal> indicates that the function
      is to be executed with the privileges of the user that calls it.
      That is the default.  <literal>SECURITY DEFINER</literal>
      specifies that the function is to be executed with the
      privileges of the user that created it.
     </para>

     <para>
      The key word <literal>EXTERNAL</literal> is allowed for SQL
      conformance, but it is optional since, unlike in SQL, this feature
      applies to all functions not only external ones.
     </para>
    </listitem>
   </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">definition</replaceable></term>

     <listitem>
      <para>
       A string constant defining the function; the meaning depends on the
       language.  It may be an internal function name, the path to an
       object file, an SQL command, or text in a procedural language.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></literal></term>

     <listitem>
      <para>
       This form of the <literal>AS</literal> clause is used for
       dynamically loadable C language functions when the function name
       in the C language source code is not the same as the name of
       the SQL function. The string <replaceable
       class="parameter">obj_file</replaceable> is the name of the
       file containing the dynamically loadable object, and
       <replaceable class="parameter">link_symbol</replaceable> is the
       function's link symbol, that is, the name of the function in the C
       language source code.  If the link symbol is omitted, it is assumed
       to be the same as the name of the SQL function being defined.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">attribute</replaceable></term>

     <listitem>
      <para>
       The historical way to specify optional pieces of information
       about the function.  The following attributes may appear here:

      <variablelist>
       <varlistentry>
        <term><literal>isStrict</></term>
        <listitem>
         <para>
          Equivalent to <literal>STRICT</literal> or <literal>RETURNS NULL ON NULL INPUT</literal>.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><literal>isCachable</></term>
        <listitem>
         <para>
          <literal>isCachable</literal> is an obsolete equivalent of
          <literal>IMMUTABLE</literal>; it's still accepted for
          backwards-compatibility reasons.
         </para>
        </listitem>
       </varlistentry>

      </variablelist>

      Attribute names are not case-sensitive.
     </para>
    </listitem>
   </varlistentry>

   </variablelist>

 </refsect1>

 <refsect1 id="sql-createfunction-notes">
  <title>Notes</title>

   <para>
    Refer to <xref linkend="xfunc"> for further information on writing
    functions.
   </para>

   <para>
    The full <acronym>SQL</acronym> type syntax is allowed for
    input arguments and return value. However, some details of the
    type specification (e.g., the precision field for
    type <type>numeric</type>) are the responsibility of the
    underlying function implementation and are silently swallowed
    (i.e., not recognized or
    enforced) by the <command>CREATE FUNCTION</command> command.
   </para>

   <para>
    <productname>PostgreSQL</productname> allows function
    <firstterm>overloading</firstterm>; that is, the same name can be
    used for several different functions so long as they have distinct
    argument types.  However, the C names of all functions must be
    different, so you must give overloaded C functions different C
    names (for example, use the argument types as part of the C
    names).
   </para>

   <para>
    Two functions are considered the same if they have the same names and
    <emphasis>input</> argument types, ignoring any <literal>OUT</>
    parameters.  Thus for example these declarations conflict:
<programlisting>
CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...
</programlisting>
   </para>

   <para>
    When repeated <command>CREATE FUNCTION</command> calls refer to
    the same object file, the file is only loaded once.  To unload and
    reload the file (perhaps during development), use the <xref
    linkend="sql-load" endterm="sql-load-title"> command.
   </para>

   <para>
    Use <xref linkend="sql-dropfunction"
    endterm="sql-dropfunction-title"> to remove user-defined
    functions.
   </para>

   <para>
    It is often helpful to use dollar quoting (see <xref
    linkend="sql-syntax-dollar-quoting">) to write the function definition
    string, rather than the normal single quote syntax.  Without dollar
    quoting, any single quotes or backslashes in the function definition must
    be escaped by doubling them.
   </para>

   <para>
    To be able to define a function, the user must have the
    <literal>USAGE</literal> privilege on the language.
   </para>

 </refsect1>

 <refsect1 id="sql-createfunction-examples">
  <title>Examples</title>

  <para>
   Here are some trivial examples to help you get started.  For more
   information and examples, see <xref linkend="xfunc">.
<programlisting>
CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
</programlisting>
  </para>

  <para>
   Increment an integer, making use of an argument name, in
   <application>PL/pgSQL</application>:
<programlisting>
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;
</programlisting>
  </para>

  <para>
   Return a record containing multiple output parameters:
<programlisting>
CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);
</programlisting>
   You can do the same thing more verbosely with an explicitly named
   composite type:
<programlisting>
CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);
</programlisting>
  </para>
 </refsect1>

 
 <refsect1 id="sql-createfunction-compat">
  <title>Compatibility</title>

  <para>
   A <command>CREATE FUNCTION</command> command is defined in SQL:1999 and later.
   The <productname>PostgreSQL</productname> version is similar but
   not fully compatible.  The attributes are not portable, neither are the
   different available languages.
  </para>

  <para>
   For compatibility with some other database systems,
   <replaceable class="parameter">argmode</replaceable> can be written
   either before or after <replaceable class="parameter">argname</replaceable>.
   But only the first way is standard-compliant.
  </para>
 </refsect1>


 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-alterfunction" endterm="sql-alterfunction-title"></member>
   <member><xref linkend="sql-dropfunction" endterm="sql-dropfunction-title"></member>
   <member><xref linkend="sql-grant" endterm="sql-grant-title"></member>
   <member><xref linkend="sql-load" endterm="sql-load-title"></member>
   <member><xref linkend="sql-revoke" endterm="sql-revoke-title"></member>
   <member><xref linkend="app-createlang" endterm="app-createlang-title"></member>
  </simplelist>
 </refsect1>

</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->