plpgsql.sgml

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

      <type>anyelement</type> or <type>anyarray</type>.  In this case the
      special <literal>$0</literal> parameter is not used; the output
      parameters themselves serve the same purpose.  For example:

<programlisting>
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;
</programlisting>
     </para>
    </sect2>

  <sect2 id="plpgsql-declaration-type">
   <title>Copying Types</title>

<synopsis>
<replaceable>variable</replaceable>%TYPE
</synopsis>

   <para>
    <literal>%TYPE</literal> provides the data type of a variable or
    table column. You can use this to declare variables that will hold
    database values. For example, let's say you have a column named
    <literal>user_id</literal> in your <literal>users</literal>
    table. To declare a variable with the same data type as
    <literal>users.user_id</> you write:
<programlisting>
user_id users.user_id%TYPE;
</programlisting>
   </para>

   <para>
    By using <literal>%TYPE</literal> you don't need to know the data
    type of the structure you are referencing, and most importantly,
    if the data type of the referenced item changes in the future (for
    instance: you change the type of <literal>user_id</>
    from <type>integer</type> to <type>real</type>), you may not need
    to change your function definition.
   </para>

   <para>
    <literal>%TYPE</literal> is particularly valuable in polymorphic
    functions, since the data types needed for internal variables may
    change from one call to the next.  Appropriate variables can be
    created by applying <literal>%TYPE</literal> to the function's
    arguments or result placeholders.
   </para>

  </sect2>

    <sect2 id="plpgsql-declaration-rowtypes">
     <title>Row Types</title>

<synopsis>
<replaceable>name</replaceable> <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>;
<replaceable>name</replaceable> <replaceable>composite_type_name</replaceable>;
</synopsis>

   <para>
    A variable of a composite type is called a <firstterm>row</>
    variable (or <firstterm>row-type</> variable).  Such a variable
    can hold a whole row of a <command>SELECT</> or <command>FOR</>
    query result, so long as that query's column set matches the
    declared type of the variable.
    The individual fields of the row value
    are accessed using the usual dot notation, for example
    <literal>rowvar.field</literal>.
   </para>

   <para>
    A row variable can be declared to have the same type as the rows of
    an existing table or view, by using the
    <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>
    notation; or it can be declared by giving a composite type's name.
    (Since every table has an associated composite type of the same name,
    it actually does not matter in <productname>PostgreSQL</> whether you
    write <literal>%ROWTYPE</literal> or not.  But the form with
    <literal>%ROWTYPE</literal> is more portable.)
   </para>

   <para>
    Parameters to a function can be
    composite types (complete table rows). In that case, the
    corresponding identifier <literal>$<replaceable>n</replaceable></> will be a row variable, and fields can
    be selected from it, for example <literal>$1.user_id</literal>.
   </para>

   <para>
    Only the user-defined columns of a table row are accessible in a
    row-type variable, not the OID or other system columns (because the
    row could be from a view).  The fields of the row type inherit the
    table's field size or precision for data types such as
    <type>char(<replaceable>n</>)</type>.
   </para>

   <para>
    Here is an example of using composite types.  <structname>table1</>
    and <structname>table2</> are existing tables having at least the
    mentioned fields:

<programlisting>
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
</programlisting>
   </para>
  </sect2>

  <sect2 id="plpgsql-declaration-records">
   <title>Record Types</title>

<synopsis>
<replaceable>name</replaceable> RECORD;
</synopsis>

   <para>
    Record variables are similar to row-type variables, but they have no
    predefined structure.  They take on the actual row structure of the
    row they are assigned during a <command>SELECT</> or <command>FOR</> command.  The substructure
    of a record variable can change each time it is assigned to.
    A consequence of this is that until a record variable is first assigned
    to, it has no substructure, and any attempt to access a
    field in it will draw a run-time error.
   </para>

   <para>
    Note that <literal>RECORD</> is not a true data type, only a placeholder.
    One should also realize that when a <application>PL/pgSQL</application>
    function is declared to return type <type>record</>, this is not quite the
    same concept as a record variable, even though such a function may well
    use a record variable to hold its result.  In both cases the actual row
    structure is unknown when the function is written, but for a function
    returning <type>record</> the actual structure is determined when the
    calling query is parsed, whereas a record variable can change its row
    structure on-the-fly.
   </para>
  </sect2>

  <sect2 id="plpgsql-declaration-renaming-vars">
   <title><literal>RENAME</></title>

<synopsis>
RENAME <replaceable>oldname</replaceable> TO <replaceable>newname</replaceable>;
</synopsis>

   <para>
    Using the <literal>RENAME</literal> declaration you can change the
    name of a variable, record or row. This is primarily useful if
    <varname>NEW</varname> or <varname>OLD</varname> should be
    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.  In effect, a query like
<synopsis>
SELECT <replaceable>expression</replaceable>
</synopsis>
     is executed using the <acronym>SPI</acronym> manager. Before 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(logtxt text) RETURNS timestamp AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
        RETURN 'now';
    END;
$$ LANGUAGE plpgsql;
</programlisting>

     and

<programlisting>
CREATE FUNCTION logfunc2(logtxt text) RETURNS timestamp AS $$
    DECLARE
        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,
     <literal>'now'</literal> will be converted to a constant when the
     <command>INSERT</command> is planned, and then used in all
     invocations of <function>logfunc1</function> during the lifetime
     of the session. Needless to say, 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</>