create_function.sgml

postgresql8.3.4源码,开源数据库

    </varlistentry>

    <varlistentry>
     <term><replaceable>configuration_parameter</replaceable></term>
     <term><replaceable>value</replaceable></term>
     <listitem>
      <para>
       The <literal>SET</> clause causes the specified configuration
       parameter to be set to the specified value when the function is
       entered, and then restored to its prior value when the function exits.
       <literal>SET FROM CURRENT</> saves the session's current value of
       the parameter as the value to be applied when the function is entered.
      </para>

      <para>
       See <xref linkend="sql-set" endterm="sql-set-title"> and
       <xref linkend="runtime-config">
       for more information about allowed parameter names and values.
      </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 can 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 can 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 per session.
    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>
    If a <literal>SET</> clause is attached to a function, then
    the effects of a <command>SET LOCAL</> command executed inside the
    function for the same variable are restricted to the function: the
    configuration parameter's prior value is still restored at function exit.
    However, an ordinary
    <command>SET</> command (without <literal>LOCAL</>) overrides the
    <literal>SET</> clause, much as it would do for a previous <command>SET
    LOCAL</> command: the effects of such a command will persist after
    function exit, unless the current transaction is rolled back.
   </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-security">
  <title>Writing <literal>SECURITY DEFINER</literal> Functions Safely</title>

   <para>
    Because a <literal>SECURITY DEFINER</literal> function is executed
    with the privileges of the user that created it, care is needed to
    ensure that the function cannot be misused.  For security,
    <xref linkend="guc-search-path"> should be set to exclude any schemas
    writable by untrusted users.  This prevents
    malicious users from creating objects that mask objects used by the
    function.  Particularly important in this regard is the
    temporary-table schema, which is searched first by default, and
    is normally writable by anyone.  A secure arrangement can be had
    by forcing the temporary schema to be searched last.  To do this,
    write <literal>pg_temp</> as the last entry in <varname>search_path</>.
    This function illustrates safe usage:
   </para>

<programlisting>
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;
</programlisting>

   <para>
    Before <productname>PostgreSQL</productname> version 8.3, the
    <literal>SET</> option was not available, and so older functions may
    contain rather complicated logic to save, set, and restore
    <varname>search_path</>.  The <literal>SET</> option is far easier
    to use for this purpose.
   </para>

   <para>
    Another point to keep in mind is that by default, execute privilege
    is granted to <literal>PUBLIC</> for newly created functions
    (see <xref linkend="sql-grant" endterm="sql-grant-title"> for more
    information).  Frequently you will wish to restrict use of a security
    definer function to only some users.  To do that, you must revoke
    the default <literal>PUBLIC</> privileges and then grant execute
    privilege selectively.  To avoid having a window where the new function
    is accessible to all, create it and set the privileges within a single
    transaction.  For example:
   </para>

<programlisting>
BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;
</programlisting>

 </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>