plpgsql.sgml

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

   <para>
    Control structures are probably the most useful (and
    important) part of <application>PL/pgSQL</>. With
    <application>PL/pgSQL</>'s control structures,
    you can manipulate <productname>PostgreSQL</> data in a very
    flexible and powerful way. 
   </para>

   <sect2 id="plpgsql-statements-returning">
    <title>Returning From a Function</title>

    <para>
     There are two commands available that allow you to return data
     from a function: <command>RETURN</command> and <command>RETURN
     NEXT</command>.
    </para>

    <sect3>
     <title><command>RETURN</></title>

<synopsis>
RETURN <replaceable>expression</replaceable>;
</synopsis>

     <para>
      <command>RETURN</command> with an expression terminates the
      function and returns the value of
      <replaceable>expression</replaceable> to the caller.  This form
      is to be used for <application>PL/pgSQL</> functions that do
      not return a set.
     </para>

     <para>
      When returning a scalar type, any expression can be used. The
      expression's result will be automatically cast into the
      function's return type as described for assignments. To return a
      composite (row) value, you must write a record or row variable
      as the <replaceable>expression</replaceable>.
     </para>

     <para>
      If you declared the function with output parameters, write just
      <command>RETURN</command> with no expression.  The current values
      of the output parameter variables will be returned.
     </para>

     <para>
      If you declared the function to return <type>void</type>, a
      <command>RETURN</command> statement can be used to exit the function
      early; but do not write an expression following
      <command>RETURN</command>.
     </para>

     <para>
      The return value of a function cannot be left undefined. If
      control reaches the end of the top-level block of the function
      without hitting a <command>RETURN</command> statement, a run-time
      error will occur.  This restriction does not apply to functions
      with output parameters and functions returning <type>void</type>,
      however.  In those cases a <command>RETURN</command> statement is
      automatically executed if the top-level block finishes.
     </para>
    </sect3>

    <sect3>
     <title><command>RETURN NEXT</></title>

<synopsis>
RETURN NEXT <replaceable>expression</replaceable>;
</synopsis>

     <para>
      When a <application>PL/pgSQL</> function is declared to return
      <literal>SETOF <replaceable>sometype</></literal>, the procedure
      to follow is slightly different.  In that case, the individual
      items to return are specified in <command>RETURN NEXT</command>
      commands, and then a final <command>RETURN</command> command
      with no argument is used to indicate that the function has
      finished executing.  <command>RETURN NEXT</command> can be used
      with both scalar and composite data types; with a composite result
      type, an entire <quote>table</quote> of results will be returned.
     </para>

     <para>
      <command>RETURN NEXT</command> does not actually return from the
      function &mdash; it simply saves away the value of the expression.
      Execution then continues with the next statement in
      the <application>PL/pgSQL</> function.  As successive
      <command>RETURN NEXT</command> commands are executed, the result
      set is built up.  A final <command>RETURN</command>, which should
      have no argument, causes control to exit the function (or you can
      just let control reach the end of the function).
     </para>

     <para>
      If you declared the function with output parameters, write just
      <command>RETURN NEXT</command> with no expression.  The current values
      of the output parameter variable(s) will be saved for eventual return.
      Note that you must declare the function as returning
      <literal>SETOF record</literal> when there are
      multiple output parameters, or
      <literal>SETOF <replaceable>sometype</></literal> when there is
      just one output parameter of type <replaceable>sometype</>, in
      order to create a set-returning function with output parameters.
     </para>

     <para>
      Functions that use <command>RETURN NEXT</command> should be
      called in the following fashion:

<programlisting>
SELECT * FROM some_func();
</programlisting>

      That is, the function must be used as a table source in a
      <literal>FROM</literal> clause.
     </para>

     <note>
      <para>
       The current implementation of <command>RETURN NEXT</command>
       for <application>PL/pgSQL</> stores the entire result set
       before returning from the function, as discussed above.  That
       means that if a <application>PL/pgSQL</> function produces a
       very large result set, performance may be poor: data will be
       written to disk to avoid memory exhaustion, but the function
       itself will not return until the entire result set has been
       generated.  A future version of <application>PL/pgSQL</> may
       allow users to define set-returning functions
       that do not have this limitation.  Currently, the point at
       which data begins being written to disk is controlled by the
       <xref linkend="guc-work-mem" xreflabel="work_mem">
       configuration variable.  Administrators who have sufficient
       memory to store larger result sets in memory should consider
       increasing this parameter.
      </para>
     </note>
    </sect3>
   </sect2>

   <sect2 id="plpgsql-conditionals">
    <title>Conditionals</title>

    <para>
     <literal>IF</> statements let you execute commands based on
     certain conditions.  <application>PL/pgSQL</> has five forms of
     <literal>IF</>:
    <itemizedlist>
     <listitem>
      <para><literal>IF ... THEN</></>
     </listitem>
     <listitem>
      <para><literal>IF ... THEN ... ELSE</></>
     </listitem>
     <listitem>
      <para><literal>IF ... THEN ... ELSE IF</></>
     </listitem>
     <listitem>
      <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE</></>
     </listitem>
     <listitem>
      <para><literal>IF ... THEN ... ELSEIF ... THEN ... ELSE</></>
     </listitem>
    </itemizedlist>
    </para>

    <sect3>
     <title><literal>IF-THEN</></title>

<synopsis>
IF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
END IF;
</synopsis>

       <para>
        <literal>IF-THEN</literal> statements are the simplest form of
        <literal>IF</literal>. The statements between
        <literal>THEN</literal> and <literal>END IF</literal> will be
        executed if the condition is true. Otherwise, they are
        skipped.
       </para>

       <para>
        Example:
<programlisting>
IF v_user_id &lt;&gt; 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
</programlisting>
       </para>
     </sect3>

     <sect3>
      <title><literal>IF-THEN-ELSE</></title>

<synopsis>
IF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
ELSE
    <replaceable>statements</replaceable>
END IF;
</synopsis>

       <para>
        <literal>IF-THEN-ELSE</literal> statements add to
        <literal>IF-THEN</literal> by letting you specify an
        alternative set of statements that should be executed if the
        condition evaluates to false.
       </para>

       <para>
        Examples:
<programlisting>
IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
</programlisting>

<programlisting>
IF v_count &gt; 0 THEN 
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;
</programlisting>
     </para>
    </sect3>

     <sect3>
      <title><literal>IF-THEN-ELSE IF</></title>

       <para>
        <literal>IF</literal> statements can be nested, as in the
        following example:

<programlisting>
IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;
</programlisting>
       </para>

       <para>
        When you use this form, you are actually nesting an
        <literal>IF</literal> statement inside the
        <literal>ELSE</literal> part of an outer <literal>IF</literal>
        statement. Thus you need one <literal>END IF</literal>
        statement for each nested <literal>IF</literal> and one for the parent
        <literal>IF-ELSE</literal>.  This is workable but grows
        tedious when there are many alternatives to be checked.
        Hence the next form.
       </para>
     </sect3>

     <sect3>
      <title><literal>IF-THEN-ELSIF-ELSE</></title>

<synopsis>
IF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
<optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
<optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
    ...
</optional>
</optional>
<optional> ELSE
    <replaceable>statements</replaceable> </optional>
END IF;
</synopsis>

       <para>
        <literal>IF-THEN-ELSIF-ELSE</> provides a more convenient
        method of checking many alternatives in one statement.
        Formally it is equivalent to nested
        <literal>IF-THEN-ELSE-IF-THEN</> commands, but only one
        <literal>END IF</> is needed.
       </para>

       <para>
        Here is an example:

<programlisting>
IF number = 0 THEN
    result := 'zero';
ELSIF number &gt; 0 THEN 
    result := 'positive';
ELSIF number &lt; 0 THEN
    result := 'negative';
ELSE
    -- hmm, the only other possibility is that number is null
    result := 'NULL';
END IF;
</programlisting>
       </para>
     </sect3>

     <sect3>
      <title><literal>IF-THEN-ELSEIF-ELSE</></title>

      <para>
       <literal>ELSEIF</> is an alias for <literal>ELSIF</>.
      </para>
     </sect3>
   </sect2>

   <sect2 id="plpgsql-control-structures-loops">
    <title>Simple Loops</title>

    <indexterm zone="plpgsql-control-structures-loops">
     <primary>loop</primary>
     <secondary>in PL/pgSQL</secondary>
    </indexterm>

    <para>
     With the <literal>LOOP</>, <literal>EXIT</>,
     <literal>CONTINUE</>, <literal>WHILE</>, and <literal>FOR</>
     statements, you can arrange for your <application>PL/pgSQL</>
     function to repeat a series of commands.
    </para>

    <sect3>
     <title><literal>LOOP</></title>

<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
LOOP
    <replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>

     <para>
      <literal>LOOP</> defines an unconditional loop that is repeated
      indefinitely until terminated by an <literal>EXIT</> or
      <command>RETURN</command> statement.  The optional
      <replaceable>label</replaceable> can be used by <literal>EXIT</>
      and <literal>CONTINUE</literal> statements in nested loops to
      specify which loop the statement should be applied to.
     </para>
    </sect3>

     <sect3>
      <title><literal>EXIT</></title>

     <indexterm>
      <primary>EXIT</primary>
      <secondary>in PL/pgSQL</secondary>
     </indexterm>

<synopsis>
EXIT