plpgsql.sgml

PostgreSQL7.4.6 for Linux

    referenced by another name inside a trigger procedure.  See also
    <literal>ALIAS</literal>.
   </para>

   <para>
    Examples:
<programlisting>
RENAME id TO user_id;
RENAME this_var TO that_var;
</programlisting>
   </para>

    <note>
     <para>
      <literal>RENAME</literal> appears to be broken as of
      <productname>PostgreSQL</> 7.3.  Fixing this is of low priority,
      since <literal>ALIAS</literal> covers most of the practical uses
      of <literal>RENAME</literal>.
     </para>
    </note>
   </sect2>
  </sect1>

  <sect1 id="plpgsql-expressions">
  <title>Expressions</title>

    <para>
     All expressions used in <application>PL/pgSQL</application>
     statements are processed using the server's regular
     <acronym>SQL</acronym> executor. Expressions that appear to
     contain constants may in fact require run-time evaluation
     (e.g., <literal>'now'</literal> for the <type>timestamp</type>
     type) so it is impossible for the
     <application>PL/pgSQL</application> parser to identify real
     constant values other than the key word <literal>NULL</>. All expressions are
     evaluated internally by executing a query
<synopsis>
SELECT <replaceable>expression</replaceable>
</synopsis>
     using the <acronym>SPI</acronym> manager. For evaluation,
     occurrences of <application>PL/pgSQL</application> variable
     identifiers are replaced by parameters, and the actual values from
     the variables are passed to the executor in the parameter array.
     This allows the query plan for the <command>SELECT</command> to
     be prepared just once and then reused for subsequent
     evaluations.
    </para>

    <para>
     The evaluation done by the <productname>PostgreSQL</productname>
     main parser has some side
     effects on the interpretation of constant values. In detail there
     is a difference between what these two functions do:

<programlisting>
CREATE FUNCTION logfunc1(text) RETURNS timestamp AS '
    DECLARE
        logtxt ALIAS FOR $1;
    BEGIN
        INSERT INTO logtable VALUES (logtxt, ''now'');
        RETURN ''now'';
    END;
' LANGUAGE plpgsql;
</programlisting>

     and

<programlisting>
CREATE FUNCTION logfunc2(text) RETURNS timestamp AS '
    DECLARE
        logtxt ALIAS FOR $1;
        curtime timestamp;
    BEGIN
        curtime := ''now'';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
    END;
' LANGUAGE plpgsql;
</programlisting>
    </para>

    <para>
     In the case of <function>logfunc1</function>, the 
     <productname>PostgreSQL</productname> main parser knows when 
     preparing the plan for the <command>INSERT</command>, that the string 
     <literal>'now'</literal> should be interpreted as 
     <type>timestamp</type> because the target column of <classname>logtable</classname>
     is of that type. Thus, it will make a constant from it at this
     time and this constant value is then used in all invocations of 
     <function>logfunc1</function> during the lifetime of the
     session. Needless to say that this isn't what the
     programmer wanted.
    </para>

    <para>
     In the case of <function>logfunc2</function>, the 
     <productname>PostgreSQL</productname> main parser does not know
     what type <literal>'now'</literal> should become and therefore 
     it returns a data value of type <type>text</type> containing the string 
     <literal>now</literal>. During the ensuing assignment
     to the local variable <varname>curtime</varname>, the
     <application>PL/pgSQL</application> interpreter casts this
     string to the <type>timestamp</type> type by calling the
     <function>text_out</function> and <function>timestamp_in</function>
     functions for the conversion.  So, the computed time stamp is updated
     on each execution as the programmer expects.
    </para>

    <para>
     The mutable nature of record variables presents a problem in this
     connection.  When fields of a record variable are used in
     expressions or statements, the data types of the fields must not
     change between calls of one and the same expression, since the
     expression will be planned using the data type that is present
     when the expression is first reached.  Keep this in mind when
     writing trigger procedures that handle events for more than one
     table.  (<command>EXECUTE</command> can be used to get around
     this problem when necessary.)
    </para>
  </sect1>

  <sect1 id="plpgsql-statements">
  <title>Basic Statements</title>

   <para>
    In this section and the following ones, we describe all the statement
    types that are explicitly understood by
    <application>PL/pgSQL</application>.
    Anything not recognized as one of these statement types is presumed
    to be an SQL command and is sent to the main database engine to execute
    (after substitution of any <application>PL/pgSQL</application> variables
    used in the statement).  Thus,
    for example, the SQL commands <command>INSERT</>, <command>UPDATE</>, and
    <command>DELETE</> may be considered to be statements of
    <application>PL/pgSQL</application>, but they are not specifically
    listed here.
   </para>
   
   <sect2 id="plpgsql-statements-assignment">
    <title>Assignment</title>

    <para>
     An assignment of a value to a variable or row/record field is
     written as:
<synopsis>
<replaceable>identifier</replaceable> := <replaceable>expression</replaceable>;
</synopsis>
     As explained above, the expression in such a statement is evaluated
     by means of an SQL <command>SELECT</> command sent to the main
     database engine.  The expression must yield a single value.
    </para>

    <para>
     If the expression's result data type doesn't match the variable's
     data type, or the variable has a specific size/precision
     (like <type>char(20)</type>), the result value will be implicitly
     converted by the <application>PL/pgSQL</application> interpreter using
     the result type's output-function and 
     the variable type's input-function. Note that this could potentially
     result in run-time errors generated by the input function, if the
     string form of the result value is not acceptable to the input function.
    </para>

    <para>
     Examples:
<programlisting>
user_id := 20;
tax := subtotal * 0.06;
</programlisting>
    </para>
   </sect2>

   <sect2 id="plpgsql-select-into">
    <title><command>SELECT INTO</command></title>

    <indexterm zone="plpgsql-select-into">
     <primary>SELECT INTO</primary>
     <secondary>in PL/pgSQL</secondary>
    </indexterm>

    <para>
     The result of a <command>SELECT</command> command yielding multiple columns (but
     only one row) can be assigned to a record variable, row-type
     variable, or list of scalar variables.  This is done by:

<synopsis>
SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
</synopsis>

     where <replaceable>target</replaceable> can be a record variable, a row
     variable, or a comma-separated list of simple variables and
     record/row fields.  The <replaceable>select_expressions</replaceable>
     and the remainder of the command are the same as in regular SQL.
    </para>

    <para>
     Note that this is quite different from
     <productname>PostgreSQL</>'s normal interpretation of
     <command>SELECT INTO</command>, where the <literal>INTO</> target
     is a newly created table.  If you want to create a table from a
     <command>SELECT</> result inside a
     <application>PL/pgSQL</application> function, use the syntax
     <command>CREATE TABLE ... AS SELECT</command>.
    </para>

    <para>
     If a row or a variable list is used as target, the selected values
     must exactly match the structure of the target, or a run-time error
     occurs.  When a record variable is the target, it automatically
     configures itself to the row type of the query result columns.
    </para>

    <para>
     Except for the <literal>INTO</> clause, the <command>SELECT</>
     statement is the same as a normal SQL <command>SELECT</> command
     and can use its full power.
    </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>
     At present, the <literal>INTO</> clause can appear almost anywhere in the <command>SELECT</command>
     statement, but it is recommended to place it immediately after the <literal>SELECT</literal>
     key word as depicted above.  Future versions of
     <application>PL/pgSQL</application> may be less forgiving about
     placement of the <literal>INTO</literal> clause.
    </para>

    <para>
     You can use <literal>FOUND</literal> immediately 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;
    full_name varchar;
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>, which must be a
     <command>SELECT</command> statement, and discards the
     result. <application>PL/pgSQL</application> variables are
     substituted in 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-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>;
</synopsis>

     where <replaceable>command-string</replaceable> is an expression
     yielding a string (of type
     <type>text</type>) containing the command
     to be executed.  This string is fed literally to the SQL engine.
    </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>
    When working with dynamic commands you will have to face
    escaping of single quotes in <application>PL/pgSQL</>. Please refer to the
    overview in <xref linkend="plpgsql-quote-tips">,
    which can save you some effort.
    </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 variable tables and columns.
    </para>
  
    <para>
     The results from <command>SELECT</command> commands are discarded
     by <command>EXECUTE</command>, and <command>SELECT INTO</command>
     is not currently supported within <command>EXECUTE</command>.
     There are two ways to extract a result from a dynamically-created
     <command>SELECT</command>: one is to use the <command>FOR-IN-EXECUTE</>
     loop form described in <xref linkend="plpgsql-records-iterating">,
     and the other is to use a cursor with <command>OPEN-FOR-EXECUTE</>, as
     described in <xref linkend="plpgsql-cursor-opening">.
    </para>

    <para>
     An example:
<programlisting>