plpgsql.sgml

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

<!--
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.80.2.4 2006/02/05 02:48:20 momjian Exp $
-->

<chapter id="plpgsql"> 
  <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>

 <indexterm zone="plpgsql">
  <primary>PL/pgSQL</primary>
 </indexterm>

 <para>
  <application>PL/pgSQL</application> is a loadable procedural
  language for the <productname>PostgreSQL</productname> database
  system.  The design goals of <application>PL/pgSQL</> were to create
  a loadable procedural language that

    <itemizedlist>
     <listitem>
      <para>
       can be used to create functions and trigger procedures,
      </para>
     </listitem>
     <listitem>
      <para>
       adds control structures to the <acronym>SQL</acronym> language,
      </para>
     </listitem>
     <listitem>
      <para>
       can perform complex computations,
      </para>
     </listitem>
     <listitem>
      <para>
       inherits all user-defined types, functions, and operators,
      </para>
     </listitem>
     <listitem>
      <para>
       can be defined to be trusted by the server,
      </para>
     </listitem>
     <listitem>
      <para>
       is easy to use.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    Except for input/output conversion and calculation functions
    for user-defined types, anything that can be defined in C language
    functions can also be done with <application>PL/pgSQL</application>.
    For example, it is possible to
    create complex conditional computation functions and later use
    them to define operators or use them in index expressions.
   </para>

  <sect1 id="plpgsql-overview">
   <title>Overview</title>

   <para>
    The <application>PL/pgSQL</> call handler parses the function's source text and
    produces an internal binary instruction tree the first time the
    function is called (within each session).  The instruction tree
    fully translates the 
    <application>PL/pgSQL</> statement structure, but individual
    <acronym>SQL</acronym> expressions and <acronym>SQL</acronym> commands
    used in the function are not translated immediately.
   </para>

   <para>
    As each expression and <acronym>SQL</acronym> command is first
    used in the function, the <application>PL/pgSQL</> interpreter
    creates a prepared execution plan (using the
    <acronym>SPI</acronym> manager's <function>SPI_prepare</function>
    and <function>SPI_saveplan</function>
    functions).<indexterm><primary>preparing a query</><secondary>in
    PL/pgSQL</></> Subsequent visits to that expression or command
    reuse the prepared plan.  Thus, a function with conditional code
    that contains many statements for which execution plans might be
    required will only prepare and save those plans that are really
    used during the lifetime of the database connection.  This can
    substantially reduce the total amount of time required to parse
    and generate execution plans for the statements in a
    <application>PL/pgSQL</> function. A disadvantage is that errors
    in a specific expression or command may not be detected until that
    part of the function is reached in execution.
   </para>

   <para>
    Once <application>PL/pgSQL</> has made an execution plan for a particular
    command in a function, it will reuse that plan for the life of the
    database connection.  This is usually a win for performance, but it
    can cause some problems if you dynamically
    alter your database schema. For example:

<programlisting>
CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
    -- declarations
BEGIN
    PERFORM my_function();
END;
$$ LANGUAGE plpgsql;
</programlisting>

    If you execute the above function, it will reference the OID for
    <function>my_function()</function> in the execution plan produced for
    the <command>PERFORM</command> statement. Later, if you
    drop and recreate <function>my_function()</function>, then
    <function>populate()</function> will not be able to find
    <function>my_function()</function> anymore. You would then have to
    recreate <function>populate()</function>, or at least start a new
    database session so that it will be compiled afresh. Another way
    to avoid this problem is to use <command>CREATE OR REPLACE
    FUNCTION</command> when updating the definition of
    <function>my_function</function> (when a function is
    <quote>replaced</quote>, its OID is not changed).
   </para>

   <para>
    Because <application>PL/pgSQL</application> saves execution plans
    in this way, SQL commands that appear directly in a
    <application>PL/pgSQL</application> function must refer to the
    same tables and columns on every execution; that is, you cannot use
    a parameter as the name of a table or column in an SQL command.  To get
    around this restriction, you can construct dynamic commands using
    the <application>PL/pgSQL</application> <command>EXECUTE</command>
    statement &mdash; at the price of constructing a new execution plan on
    every execution.
   </para>

   <note>
        <para>
         The <application>PL/pgSQL</application>
         <command>EXECUTE</command> statement is not related to the
         <xref linkend="sql-execute" endterm="sql-execute-title"> SQL
         statement supported by the
         <productname>PostgreSQL</productname> server. The server's
         <command>EXECUTE</command> statement cannot be used within
         <application>PL/pgSQL</> functions (and is not needed).
        </para>
   </note>

  <sect2 id="plpgsql-advantages">
   <title>Advantages of Using <application>PL/pgSQL</application></title>

    <para>
     <acronym>SQL</acronym> is the language <productname>PostgreSQL</>
     and most other relational databases use as query language. It's
     portable and easy to learn. But every <acronym>SQL</acronym>
     statement must be executed individually by the database server.
    </para>

    <para>
     That means that your client application must send each query to
     the database server, wait for it to be processed, receive the
     results, do some computation, then send other queries to the
     server. All this incurs interprocess communication and may also
     incur network overhead if your client is on a different machine
     than the database server.
    </para>

    <para>
     With <application>PL/pgSQL</application> you can group a block of computation and a
     series of queries <emphasis>inside</emphasis> the
     database server, thus having the power of a procedural
     language and the ease of use of SQL, but saving lots of
     time because you don't have the whole client/server
     communication overhead. This can make for a
     considerable performance increase.
    </para>

    <para>
     Also, with <application>PL/pgSQL</application> you can use all
     the data types, operators and functions of SQL.
    </para>
  </sect2>

  <sect2 id="plpgsql-args-results">
   <title>Supported Argument and Result Data Types</title>

    <para>
     Functions written in <application>PL/pgSQL</application> can accept
     as arguments any scalar or array data type supported by the server,
     and they can return a result of any of these types.  They can also
     accept or return any composite type (row type) specified by name.
     It is also possible to declare a <application>PL/pgSQL</application>
     function as returning <type>record</>, which means that the result
     is a row type whose columns are determined by specification in the
     calling query, as discussed in <xref linkend="queries-tablefunctions">.
    </para>

    <para>
     <application>PL/pgSQL</> functions may also be declared to accept
     and return the polymorphic types
     <type>anyelement</type> and <type>anyarray</type>.  The actual
     data types handled by a polymorphic function can vary from call to
     call, as discussed in <xref linkend="extend-types-polymorphic">.
     An example is shown in <xref linkend="plpgsql-declaration-aliases">.
    </para>

    <para>
     <application>PL/pgSQL</> functions can also be declared to return
     a <quote>set</>, or table, of any data type they can return a single
     instance of.  Such a function generates its output by executing
     <literal>RETURN NEXT</> for each desired element of the result set.
    </para>

    <para>
     Finally, a <application>PL/pgSQL</> function may be declared to return
     <type>void</> if it has no useful return value.
    </para>

    <note>
    <para>
     <application>PL/pgSQL</> does not currently have full support for
     domain types: it treats a domain the same as the underlying scalar
     type.  This means that constraints associated with the domain will
     not be enforced.  This is not an issue for function arguments, but
     it is a hazard if you declare a <application>PL/pgSQL</> function
     as returning a domain type.
    </para>
    </note>

    <para>
     <application>PL/pgSQL</> functions can also be declared with output
     parameters in place of an explicit specification of the return type.
     This does not add any fundamental capability to the language, but
     it is often convenient, especially for returning multiple values.
    </para>

    <para>
     Specific examples appear in
     <xref linkend="plpgsql-declaration-aliases"> and
     <xref linkend="plpgsql-statements-returning">.
    </para>
  </sect2>
 </sect1>

 <sect1 id="plpgsql-development-tips">
  <title>Tips for Developing in <application>PL/pgSQL</application></title>

   <para>
    One good way to develop in
    <application>PL/pgSQL</> is to use the text editor of your
    choice to create your functions, and in another window, use
    <application>psql</application> to load and test those functions.
    If you are doing it this way, it
    is a good idea to write the function using <command>CREATE OR
    REPLACE FUNCTION</>. That way you can just reload the file to update
    the function definition.  For example:
<programlisting>
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
          ....
$$ LANGUAGE plpgsql;
</programlisting>
   </para>

   <para>
    While running <application>psql</application>, you can load or reload such
    a function definition file with
<programlisting>
\i filename.sql
</programlisting>
    and then immediately issue SQL commands to test the function.
   </para>

   <para>
    Another good way to develop in <application>PL/pgSQL</> is with a
    GUI database access tool that facilitates development in a
    procedural language. One example of such as a tool is
    <application>PgAccess</>, although others exist. These tools often
    provide convenient features such as escaping single quotes and
    making it easier to recreate and debug functions.
   </para>

  <sect2 id="plpgsql-quote-tips">
   <title>Handling of Quotation Marks</title>

   <para>
    The code of a <application>PL/pgSQL</> function is specified in
    <command>CREATE FUNCTION</command> as a string literal.  If you
    write the string literal in the ordinary way with surrounding
    single quotes, then any single quotes inside the function body
    must be doubled; likewise any backslashes must be doubled.
    Doubling quotes is at best tedious, and in more complicated cases
    the code can become downright incomprehensible, because you can
    easily find yourself needing half a dozen or more adjacent quote marks.
    It's recommended that you instead write the function body as a
    <quote>dollar-quoted</> string literal (see <xref
    linkend="sql-syntax-dollar-quoting">).  In the dollar-quoting
    approach, you never double any quote marks, but instead take care to
    choose a different dollar-quoting delimiter for each level of
    nesting you need.  For example, you might write the <command>CREATE
    FUNCTION</command> command as
<programlisting>
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;
</programlisting>
    Within this, you might use quote marks for simple literal strings in
    SQL commands and <literal>$$</> to delimit fragments of SQL commands
    that you are assembling as strings.  If you need to quote text that
    includes <literal>$$</>, you could use <literal>$Q$</>, and so on.
   </para>

   <para>
    The following chart shows what you have to do when writing quote
    marks without dollar quoting.  It may be useful when translating
    pre-dollar quoting code into something more comprehensible.
  </para>

  <variablelist>
   <varlistentry>
    <term>1 quotation mark</term>
    <listitem>
     <para>
      To begin and end the function body, for example:
<programlisting>
CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;
</programlisting>
      Anywhere within a single-quoted function body, quote marks
      <emphasis>must</> appear in pairs.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>2 quotation marks</term>
    <listitem>
     <para>
      For string literals inside the function body, for example:
<programlisting>
a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';
</programlisting>
      In the dollar-quoting approach, you'd just write
<programlisting>
a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';
</programlisting>
      which is exactly what the <application>PL/pgSQL</> parser would see
      in either case.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>4 quotation marks</term>
    <listitem>
     <para>
      When you need a single quotation mark in a string constant inside the
      function body, for example:
<programlisting>
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
</programlisting>
      The value actually appended to <literal>a_output</literal> would be:
      <literal> AND name LIKE 'foobar' AND xyz</literal>.
     </para>
     <para>