plpgsql.sgml

PostgreSQL7.4.6 for Linux

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>
   </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>WHILE</>,
     and <literal>FOR</> statements, you can arrange for your
     <application>PL/pgSQL</application> 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;
</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 label can be used by <literal>EXIT</> statements in
      nested loops to specify which level of nesting should be
      terminated.
     </para>
    </sect3>

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

<synopsis>
EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>expression</replaceable> </optional>;
</synopsis>

       <para>
        If no <replaceable>label</replaceable> is given,
        the innermost loop is terminated and the
        statement following <literal>END LOOP</> is executed next.
        If <replaceable>label</replaceable> is given, it
        must be the label of the current or some outer level of nested loop
        or block. Then the named loop or block is terminated and control
        continues with the statement after the loop's/block's corresponding
        <literal>END</>.
       </para>

       <para>
        If <literal>WHEN</> is present, loop exit occurs only if the specified condition
        is true, otherwise control passes to the statement after <literal>EXIT</>.
       </para>

       <para>
        Examples:
<programlisting>
LOOP
    -- some computations
    IF count &gt; 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    -- some computations
    EXIT WHEN count &gt; 0;  -- same result as previous example
END LOOP;

BEGIN
    -- some computations
    IF stocks &gt; 100000 THEN
        EXIT;  -- invalid; cannot use EXIT outside of LOOP
    END IF;
END;
</programlisting>
       </para>
     </sect3>

     <sect3>
      <title><literal>WHILE</></title>

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

       <para>
        The <literal>WHILE</> statement repeats a
        sequence of statements so long as the condition expression
        evaluates to true.  The condition is checked just before
        each entry to the loop body.
       </para>

       <para>
        For example:
<programlisting>
WHILE amount_owed &gt; 0 AND gift_certificate_balance &gt; 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT boolean_expression LOOP
    -- some computations here
END LOOP;
</programlisting>
       </para>
     </sect3>

     <sect3>
      <title><literal>FOR</> (integer variant)</title>

<synopsis>
<optional>&lt;&lt;<replaceable>label</replaceable>&gt;&gt;</optional>
FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> LOOP
    <replaceable>statements</replaceable>
END LOOP;
</synopsis>

       <para>
        This form of <literal>FOR</> creates a loop that iterates over a range of integer
        values. The variable 
        <replaceable>name</replaceable> is automatically defined as type
        <type>integer</> and exists only inside the loop. The two expressions giving
        the lower and upper bound of the range are evaluated once when entering
        the loop. The iteration step is normally 1, but is -1 when <literal>REVERSE</> is
        specified.
       </para>

       <para>
        Some examples of integer <literal>FOR</> loops:
<programlisting>
FOR i IN 1..10 LOOP
    -- some computations here
    RAISE NOTICE ''i is %'', i;
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- some computations here
END LOOP;
</programlisting>
       </para>

       <para>
        If the lower bound is greater than the upper bound (or less than,
        in the <literal>REVERSE</> case), the loop body is not
        executed at all.  No error is raised.
       </para>
     </sect3>
   </sect2>

   <sect2 id="plpgsql-records-iterating">
    <title>Looping Through Query Results</title>

    <para>
     Using a different type of <literal>FOR</> loop, you can iterate through
     the results of a query and manipulate that data
     accordingly. The syntax is:
<synopsis>
<optional>&lt;&lt;<replaceable>label</replaceable>&gt;&gt;</optional>
FOR <replaceable>record_or_row</replaceable> IN <replaceable>query</replaceable> LOOP
    <replaceable>statements</replaceable>
END LOOP;
</synopsis>
     The record or row variable is successively assigned each row
     resulting from the query (a <command>SELECT</command> command) and the loop
     body is executed for each row. Here is an example:
<programlisting>
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS '
DECLARE
    mviews RECORD;
BEGIN
    PERFORM cs_log(''Refreshing materialized views...'');

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

        -- Now "mviews" has one record from cs_materialized_views

        PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...'');
        EXECUTE ''TRUNCATE TABLE  '' || quote_ident(mviews.mv_name);
        EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query;
    END LOOP;

    PERFORM cs_log(''Done refreshing materialized views.'');
    RETURN 1;
END;
' LANGUAGE plpgsql;
</programlisting>

     If the loop is terminated by an <literal>EXIT</> statement, the last
     assigned row value is still accessible after the loop.
    </para>

    <para>
     The <literal>FOR-IN-EXECUTE</> statement is another way to iterate over
     records:
<synopsis>
<optional>&lt;&lt;<replaceable>label</replaceable>&gt;&gt;</optional>
FOR <replaceable>record_or_row</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP 
    <replaceable>statements</replaceable>
END LOOP;
</synopsis>
     This is like the previous form, except that the source
     <command>SELECT</command> statement is specified as a string
     expression, which is evaluated and replanned on each entry to
     the <literal>FOR</> loop.  This allows the programmer to choose the speed of
     a preplanned query or the flexibility of a dynamic query, just
     as with a plain <command>EXECUTE</command> statement.
    </para>

    <note>
    <para>
     The <application>PL/pgSQL</> parser presently distinguishes the
     two kinds of <literal>FOR</> loops (integer or query result) by checking
     whether the target variable mentioned just after <literal>FOR</> has been
     declared as a record or row variable.  If not, it's presumed to be
     an integer <literal>FOR</> loop.  This can cause rather nonintuitive error
     messages when the true problem is, say, that one has
     misspelled the variable name after the <literal>FOR</>.  Typically
     the complaint will be something like <literal>missing ".." at end of SQL
     expression</>.
    </para>
    </note>
  </sect2>
  </sect1>

  <sect1 id="plpgsql-cursors">
   <title>Cursors</title>

   <indexterm zone="plpgsql-cursors">
    <primary>cursor</primary>
    <secondary>in PL/pgSQL</secondary>
   </indexterm>

   <para>
    Rather than executing a whole query at once, it is possible to set
    up a <firstterm>cursor</> that encapsulates the query, and then read
    the query result a few rows at a time. One reason for doing this is
    to avoid memory overrun when the result contains a large number of
    rows. (However, <application>PL/pgSQL</> users do not normally need
    to worry about that, since <literal>FOR</> loops automatically use a cursor
    internally to avoid memory problems.) A more interesting usage is to
    return a reference to a cursor that a function has created, allowing the
    caller to read the rows. This provides an efficient way to return
    large row sets from functions.
   </para>
   
   <sect2 id="plpgsql-cursor-declarations">
    <title>Declaring Cursor Variables</title>

    <para>
     All access to cursors in <application>PL/pgSQL</> goes through
     cursor variables, which are always of the special data type
     <type>refcursor</>.  One way to create a cursor variable
     is just to declare it as a variable of type <type>refcursor</>.
     Another way is to use the cursor declaration syntax,
     which in general is:
<synopsis>
<replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable> ;
</synopsis>
     (<literal>FOR</> may be replaced by <literal>IS</> for
     <productname>Oracle</productname> compatibility.)
     <replaceable>arguments</replaceable>, if specified, is a
     comma-separated list of pairs <literal><replaceable>name</replaceable>
     <replaceable>datatype</replaceable></literal> that define names to be
     replaced by parameter values in the given query.  The actual
     values to substitute for these names will be specified later,
     when the cursor is opened.
    </para>
    <para>
     Some examples:
<programlisting>
DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
</programlisting>
     All three of these variables have the data type <type>refcursor</>,
     but the first may be used with any query, while the second has
     a fully specified query already <firstterm>bound</> to it, and the last
     has a parameterized query bound to it.  (<literal>key</> will be
     replaced by an integer parameter value when the cursor is opened.)
     The variable <literal>curs1</>
     is said to be <firstterm>unbound</> since it is not bound to
     any particular query.
    </para>
   </sect2>

   <sect2 id="plpgsql-cursor-opening">
    <title>Opening Cursors</title>

    <para>
     Before a cursor can be used to retrieve rows, it must be
     <firstterm>opened</>. (This is the equivalent action to the SQL
     command <command>DECL