plpgsql.sgml

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

     statement is the same as a normal SQL <command>SELECT</> command
     and can use its full power.
    </para>

    <para>
     The <literal>INTO</> clause can appear almost anywhere in the
     <command>SELECT</command> statement.  Customarily it is written
     either just after <literal>SELECT</> as shown above, or
     just before <literal>FROM</> &mdash; that is, either just before
     or just after the list of <replaceable>select_expressions</replaceable>.
    </para>

    <para>
     If the query returns zero rows, null values are assigned to the
     target(s).  If the query returns multiple rows, the first
     row is assigned to the target(s) and the rest are discarded.
     (Note that <quote>the first row</> is not well-defined unless you've
     used <literal>ORDER BY</>.)
    </para>

    <para>
     You can check the special <literal>FOUND</literal> variable (see
     <xref linkend="plpgsql-statements-diagnostics">) after a
     <command>SELECT INTO</command> statement to determine whether the
     assignment was successful, that is, at least one row was was returned by
     the query. For example:

<programlisting>
SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;
</programlisting>
    </para>

    <para>
     To test for whether a record/row result is null, you can use the
     <literal>IS NULL</literal> conditional.  There is, however, no
     way to tell whether any additional rows might have been
     discarded.  Here is an example that handles the case where no
     rows have been returned:
<programlisting>
DECLARE
    users_rec RECORD;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

    IF users_rec.homepage IS NULL THEN
        -- user entered no homepage, return "http://"
        RETURN 'http://';
    END IF;
END;
</programlisting>
    </para>
   </sect2>

   <sect2 id="plpgsql-statements-perform">
    <title>Executing an Expression or Query With No Result</title>

    <para>
     Sometimes one wishes to evaluate an expression or query but
     discard the result (typically because one is calling a function
     that has useful side-effects but no useful result value).  To do
     this in <application>PL/pgSQL</application>, use the
     <command>PERFORM</command> statement:

<synopsis>
PERFORM <replaceable>query</replaceable>;
</synopsis>

     This executes <replaceable>query</replaceable> and discards the
     result.  Write the <replaceable>query</replaceable> the same
     way as you would in an SQL <command>SELECT</> command, but replace the
     initial keyword <command>SELECT</> with <command>PERFORM</command>.
     <application>PL/pgSQL</application> variables will be
     substituted into the query as usual.  Also, the special variable
     <literal>FOUND</literal> is set to true if the query produced at
     least one row or false if it produced no rows.
    </para>

    <note>
     <para>
      One might expect that <command>SELECT</command> with no
      <literal>INTO</> clause would accomplish this result, but at
      present the only accepted way to do it is
      <command>PERFORM</command>.
     </para>
    </note>

    <para>
     An example:
<programlisting>
PERFORM create_mv('cs_session_page_requests_mv', my_query);
</programlisting>
    </para>
   </sect2>

   <sect2 id="plpgsql-statements-null">
    <title>Doing Nothing At All</title>

    <para>
     Sometimes a placeholder statement that does nothing is useful.
     For example, it can indicate that one arm of an if/then/else
     chain is deliberately empty.  For this purpose, use the
     <command>NULL</command> statement:

<synopsis>
NULL;
</synopsis>
    </para>

    <para>
     For example, the following two fragments of code are equivalent:
<programlisting>
    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            NULL;  -- ignore the error
    END;
</programlisting>

<programlisting>
    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN  -- ignore the error
    END;
</programlisting>
     Which is preferable is a matter of taste.
    </para>

    <note>
     <para>
      In Oracle's PL/SQL, empty statement lists are not allowed, and so
      <command>NULL</> statements are <emphasis>required</> for situations
      such as this.  <application>PL/pgSQL</application> allows you to
      just write nothing, instead.
     </para>
    </note>

   </sect2>

   <sect2 id="plpgsql-statements-executing-dyn">
    <title>Executing Dynamic Commands</title>

    <para>
     Oftentimes you will want to generate dynamic commands inside your
     <application>PL/pgSQL</application> functions, that is, commands
     that will involve different tables or different data types each
     time they are executed.  <application>PL/pgSQL</application>'s
     normal attempts to cache plans for commands will not work in such
     scenarios.  To handle this sort of problem, the
     <command>EXECUTE</command> statement is provided:

<synopsis>
EXECUTE <replaceable class="command">command-string</replaceable> [ INTO <replaceable>target</replaceable> ];
</synopsis>

     where <replaceable>command-string</replaceable> is an expression
     yielding a string (of type <type>text</type>) containing the
     command to be executed and <replaceable>target</replaceable> is a
     record variable, row variable, or a comma-separated list of
     simple variables and record/row fields.
    </para>

    <para>
     Note in particular that no substitution of <application>PL/pgSQL</>
     variables is done on the command string.  The values of variables must
     be inserted in the command string as it is constructed.
    </para>

    <para>
     Unlike all other commands in <application>PL/pgSQL</>, a command
     run by an <command>EXECUTE</command> statement is not prepared
     and saved just once during the life of the session.  Instead, the
     command is prepared each time the statement is run. The command
     string can be dynamically created within the function to perform
     actions on different tables and columns.
    </para>

    <para>
     The <literal>INTO</literal> clause specifies where the results of
     a <command>SELECT</command> command should be assigned. If a row
     or variable list is provided, it must exactly match the structure
     of the results produced by the <command>SELECT</command> (when a
     record variable is used, it will configure itself to match the
     result's structure automatically). If multiple rows are returned,
     only the first will be assigned to the <literal>INTO</literal>
     variable. If no rows are returned, NULL is assigned to the
     <literal>INTO</literal> variable. If no <literal>INTO</literal>
     clause is specified, the results of a <command>SELECT</command>
     command are discarded.
    </para>

    <para>
     <command>SELECT INTO</command> is not currently supported within
     <command>EXECUTE</command>.
    </para>

    <para>
     When working with dynamic commands you will often have to handle escaping
     of single quotes.  The recommended method for quoting fixed text in your
     function body is dollar quoting.  (If you have legacy code that does
     not use dollar quoting, please refer to the
     overview in <xref linkend="plpgsql-quote-tips">, which can save you
     some effort when translating said code to a more reasonable scheme.)
    </para>

    <para>
     Dynamic values that are to be inserted into the constructed
     query require special handling since they might themselves contain
     quote characters.
     An example (this assumes that you are using dollar quoting for the
     function as a whole, so the quote marks need not be doubled):
<programlisting>
EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);
</programlisting>
    </para>

    <indexterm>
     <primary>quote_ident</primary>
     <secondary>use in PL/PgSQL</secondary>
    </indexterm>

    <indexterm>
     <primary>quote_literal</primary>
     <secondary>use in PL/PgSQL</secondary>
    </indexterm>

    <para>
     This example demonstrates the use of the
     <function>quote_ident</function> and
     <function>quote_literal</function> functions.  For safety,
     expressions containing column and table identifiers should be
     passed to <function>quote_ident</function>.  Expressions containing
     values that should be literal strings in the constructed command
     should be passed to <function>quote_literal</function>.  Both
     take the appropriate steps to return the input text enclosed in
     double or single quotes respectively, with any embedded special
     characters properly escaped.
    </para>

    <para>
     Note that dollar quoting is only useful for quoting fixed text.
     It would be a very bad idea to try to do the above example as
<programlisting>
EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);
</programlisting>
     because it would break if the contents of <literal>newvalue</>
     happened to contain <literal>$$</>.  The same objection would
     apply to any other dollar-quoting delimiter you might pick.
     So, to safely quote text that is not known in advance, you
     <emphasis>must</> use <function>quote_literal</function>.
    </para>

    <para>
     A much larger example of a dynamic command and
     <command>EXECUTE</command> can be seen in <xref
     linkend="plpgsql-porting-ex2">, which builds and executes a
     <command>CREATE FUNCTION</> command to define a new function.
    </para>
   </sect2>

   <sect2 id="plpgsql-statements-diagnostics">
    <title>Obtaining the Result Status</title>

    <para>
     There are several ways to determine the effect of a command. The
     first method is to use the <command>GET DIAGNOSTICS</command>
     command, which has the form:

<synopsis>
GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>

     This command allows retrieval of system status indicators.  Each
     <replaceable>item</replaceable> is a key word identifying a state
     value to be assigned to the specified variable (which should be
     of the right data type to receive it).  The currently available
     status items are <varname>ROW_COUNT</>, the number of rows
     processed by the last <acronym>SQL</acronym> command sent down to
     the <acronym>SQL</acronym> engine, and <varname>RESULT_OID</>,
     the OID of the last row inserted by the most recent
     <acronym>SQL</acronym> command.  Note that <varname>RESULT_OID</>
     is only useful after an <command>INSERT</command> command into a
     table containing OIDs.
    </para>

    <para>
     An example:
<programlisting>
GET DIAGNOSTICS integer_var = ROW_COUNT;
</programlisting>
    </para>

    <para>
     The second method to determine the effects of a command is to check the
     special variable named <literal>FOUND</literal>, which is of
     type <type>boolean</type>.  <literal>FOUND</literal> starts out
     false within each <application>PL/pgSQL</application> function call.
     It is set by each of the following types of statements:
         <itemizedlist>
          <listitem>
           <para>
                A <command>SELECT INTO</command> statement sets
                <literal>FOUND</literal> true if it returns a row, false if no
                row is returned.
           </para>
          </listitem>
          <listitem>
           <para>
                A <command>PERFORM</> statement sets <literal>FOUND</literal>
                true if it produces (and discards) a row, false if no row is
                produced.
           </para>
          </listitem>
          <listitem>
           <para>
                <command>UPDATE</>, <command>INSERT</>, and <command>DELETE</>
                statements set <literal>FOUND</literal> true if at least one
                row is affected, false if no row is affected.
           </para>
          </listitem>
          <listitem>
           <para>
                A <command>FETCH</> statement sets <literal>FOUND</literal>
                true if it returns a row, false if no row is returned.
           </para>
          </listitem>
          <listitem>
           <para>
                A <command>FOR</> statement sets <literal>FOUND</literal> true
                if it iterates one or more times, else false.  This applies to
                all three variants of the <command>FOR</> statement (integer
                <command>FOR</> loops, record-set <command>FOR</> loops, and
                dynamic record-set <command>FOR</>
                loops). <literal>FOUND</literal> is set this way when the
                <command>FOR</> loop exits; inside the execution of the loop,
                <literal>FOUND</literal> is not modified by the
                <command>FOR</> statement, although it may be changed by the
                execution of other statements within the loop body.
           </para>
          </listitem>
         </itemizedlist>

     <literal>FOUND</literal> is a local variable within each
     <application>PL/pgSQL</application> function; any changes to it
     affect only the current function.
    </para>

   </sect2>
  </sect1>

  <sect1 id="plpgsql-control-structures">
   <title>Control Structures</title>