plpgsql.sgml

PostgreSQL7.4.6 for Linux

EXECUTE ''UPDATE tbl SET ''
        || quote_ident(colname)
        || '' = ''
        || quote_literal(newvalue)
        || '' WHERE ...'';
</programlisting>
    </para>

    <para>
     This example shows use of the functions
     <function>quote_ident(<type>text</type>)</function> and
     <function>quote_literal(<type>text</type>)</function>.<indexterm><primary>quote_ident</><secondary>use
     in
     PL/pgSQL</></indexterm><indexterm><primary>quote_literal</><secondary>use
     in PL/pgSQL</></indexterm> For safety, variables containing column and
     table identifiers should be passed to function
     <function>quote_ident</function>.  Variables 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>
     Here is a much larger example of a dynamic command and
     <command>EXECUTE</command>:
<programlisting>
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
    referrer_keys RECORD;  -- declare a generic record to be used in a FOR
    a_output varchar(4000);
BEGIN 
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar) 
                  RETURNS varchar AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3;
                     BEGIN ''; 

    -- Notice how we scan through the results of a query in a FOR loop
    -- using the FOR &lt;record&gt; construct.

    FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
        a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' 
                 || referrer_keys.key_string || '''''''''' THEN RETURN '''''' 
                 || referrer_keys.referrer_type || ''''''; END IF;''; 
    END LOOP; 
  
    a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;''; 
 
    EXECUTE a_output; 
END; 
' LANGUAGE plpgsql;
</programlisting>
    </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.
    </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 only set 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; any changes
     to it affect only the current <application>PL/pgSQL</application>
     function.
    </para>

   </sect2>
  </sect1>

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

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

     <para>
      If you have declared the function to
      return <type>void</type>, a <command>RETURN</command> statement
      must still be specified; but in this case the expression following
      <command>RETURN</command> is optional and will be ignored if present.
     </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; in the latter case, an
      entire <quote>table</quote> of results will be returned.
     </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 is used as a table source in a <literal>FROM</literal>
      clause.
     </para>

     <para>
      <command>RETURN NEXT</command> does not actually return from the
      function; it simply saves away the value of the expression (or
      record or row variable, as appropriate for the data type being
      returned).  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.
     </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
       <varname>sort_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 four 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>
    </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 > 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>