plpgsql.sgml

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

      In the dollar-quoting approach, you'd write
<programlisting>
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
</programlisting>
      being careful that any dollar-quote delimiters around this are not
      just <literal>$$</>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>6 quotation marks</term>
    <listitem>
     <para>
      When a single quotation mark in a string inside the function body is
      adjacent to the end of that string constant, for example:
<programlisting>
a_output := a_output || '' AND name LIKE ''''foobar''''''
</programlisting>
      The value appended to <literal>a_output</literal> would then be:
      <literal> AND name LIKE 'foobar'</literal>.
     </para>
     <para>
      In the dollar-quoting approach, this becomes
<programlisting>
a_output := a_output || $$ AND name LIKE 'foobar'$$
</programlisting>
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>10 quotation marks</term>
    <listitem>
     <para>
      When you want two single quotation marks in a string constant (which
      accounts for 8 quotation marks) and this is adjacent to the end of that
      string constant (2 more).  You will probably only need that if
      you are writing a function that generates other functions, as in
      <xref linkend="plpgsql-porting-ex2">.
      For example:
<programlisting>
a_output := a_output || '' if v_'' || 
    referrer_keys.kind || '' like '''''''''' 
    || referrer_keys.key_string || '''''''''' 
    then return ''''''  || referrer_keys.referrer_type 
    || ''''''; end if;''; 
</programlisting>
      The value of <literal>a_output</literal> would then be:
<programlisting>
if v_... like ''...'' then return ''...''; end if;
</programlisting>
     </para>
     <para>
      In the dollar-quoting approach, this becomes
<programlisting>
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
    then return '$$  || referrer_keys.referrer_type 
    || $$'; end if;$$; 
</programlisting>
      where we assume we only need to put single quote marks into
      <literal>a_output</literal>, because it will be re-quoted before use.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

   <para>
    A variant approach is to escape quotation marks in the function body
    with a backslash rather than by doubling them.  With this method
    you'll find yourself writing things like <literal>\'\'</> instead
    of <literal>''''</>.  Some find this easier to keep track of, some
    do not.
   </para>
  </sect2>
 </sect1>

 <sect1 id="plpgsql-structure">
  <title>Structure of <application>PL/pgSQL</application></title>

  <para>
   <application>PL/pgSQL</application> is a block-structured language.
   The complete text of a function definition must be a
   <firstterm>block</>. A block is defined as:

<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
<optional> DECLARE
    <replaceable>declarations</replaceable> </optional>
BEGIN
    <replaceable>statements</replaceable>
END <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
    </para>

    <para>
     Each declaration and each statement within a block is terminated
     by a semicolon.  A block that appears within another block must
     have a semicolon after <literal>END</literal>, as shown above;
     however the final <literal>END</literal> that
     concludes a function body does not require a semicolon.
    </para>

    <para>
     All key words and identifiers can be written in mixed upper and
     lower case.  Identifiers are implicitly converted to lowercase
     unless double-quoted.
    </para>

    <para>
     There are two types of comments in <application>PL/pgSQL</>. A double
     dash (<literal>--</literal>) starts a comment that extends to the end of
     the line. A <literal>/*</literal> starts a block comment that extends to
     the next occurrence of <literal>*/</literal>.  Block comments cannot be
     nested, but double dash comments can be enclosed into a block comment and
     a double dash can hide the block comment delimiters <literal>/*</literal>
     and <literal>*/</literal>.
    </para>

    <para>
     Any statement in the statement section of a block
     can be a <firstterm>subblock</>.  Subblocks can be used for
     logical grouping or to localize variables to a small group
     of statements.
    </para>

    <para>
     The variables declared in the declarations section preceding a
     block are initialized to their default values every time the
     block is entered, not only once per function call. For example:
<programlisting>
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Quantity here is 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Quantity here is 80
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Quantity here is 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;
</programlisting>
    </para>

    <para>
     It is important not to confuse the use of
     <command>BEGIN</>/<command>END</> for grouping statements in
     <application>PL/pgSQL</> with the database commands for transaction
     control.  <application>PL/pgSQL</>'s <command>BEGIN</>/<command>END</>
     are only for grouping; they do not start or end a transaction.
     Functions and trigger procedures are always executed within a transaction
     established by an outer query &mdash; they cannot start or commit that
     transaction, since there would be no context for them to execute in.
     However, a block containing an <literal>EXCEPTION</> clause effectively
     forms a subtransaction that can be rolled back without affecting the
     outer transaction.  For more about that see <xref
     linkend="plpgsql-error-trapping">.
    </para>
  </sect1>

  <sect1 id="plpgsql-declarations">
    <title>Declarations</title>

    <para>
     All variables used in a block must be declared in the
     declarations section of the block. 
     (The only exception is that the loop variable of a <literal>FOR</> loop
     iterating over a range of integer values is automatically declared as an
     integer variable.)
    </para>

    <para>
     <application>PL/pgSQL</> variables can have any SQL data type, such as
     <type>integer</type>, <type>varchar</type>, and
     <type>char</type>.
    </para>

    <para>
     Here are some examples of variable declarations:
<programlisting>
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
</programlisting>
    </para>

    <para>
     The general syntax of a variable declaration is:
<synopsis>
<replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
</synopsis>
      The <literal>DEFAULT</> clause, if given, specifies the initial value assigned
      to the variable when the block is entered.  If the <literal>DEFAULT</> clause
      is not given then the variable is initialized to the
      <acronym>SQL</acronym> null value. 
      The <literal>CONSTANT</> option prevents the variable from being assigned to,
      so that its value remains constant for the duration of the block.
      If <literal>NOT NULL</>
      is specified, an assignment of a null value results in a run-time
      error. All variables declared as <literal>NOT NULL</>
      must have a nonnull default value specified.
     </para>

     <para>
      The default value is evaluated every time the block is entered. So,
      for example, assigning <literal>now()</literal> to a variable of type
      <type>timestamp</type> causes the variable to have the
      time of the current function call, not the time when the function was
      precompiled.
     </para>

     <para>
      Examples:
<programlisting>
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;
</programlisting>
     </para>

    <sect2 id="plpgsql-declaration-aliases">
     <title>Aliases for Function Parameters</title>

     <para>
      Parameters passed to functions are named with the identifiers
      <literal>$1</literal>, <literal>$2</literal>,
      etc.  Optionally, aliases can be declared for
      <literal>$<replaceable>n</replaceable></literal>
      parameter names for increased readability.  Either the alias or the
      numeric identifier can then be used to refer to the parameter value.
     </para>

     <para>
      There are two ways to create an alias.  The preferred way is to give a
      name to the parameter in the <command>CREATE FUNCTION</command> command,
      for example:
<programlisting>
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
</programlisting>
      The other way, which was the only way available before
      <productname>PostgreSQL</productname> 8.0, is to explicitly
      declare an alias, using the declaration syntax

<synopsis>
<replaceable>name</replaceable> ALIAS FOR $<replaceable>n</replaceable>;
</synopsis>

      The same example in this style looks like
<programlisting>
CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
</programlisting>
      Some more examples:
<programlisting>
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- some computations using v_string and index here
END;
$$ LANGUAGE plpgsql;


CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;
</programlisting>
     </para>

     <para>
      When a <application>PL/pgSQL</application> function is declared
      with output parameters, the output parameters are given
      <literal>$<replaceable>n</replaceable></literal> names and optional
      aliases in just the same way as the normal input parameters.  An
      output parameter is effectively a variable that starts out NULL;
      it should be assigned to during the execution of the function.
      The final value of the parameter is what is returned.  For instance,
      the sales-tax example could also be done this way:

<programlisting>
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
</programlisting>

      Notice that we omitted <literal>RETURNS real</> &mdash; we could have
      included it, but it would be redundant.
     </para>

     <para>
      Output parameters are most useful when returning multiple values.
      A trivial example is:

<programlisting>
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;
</programlisting>

      As discussed in <xref linkend="xfunc-output-parameters">, this
      effectively creates an anonymous record type for the function's
      results.  If a <literal>RETURNS</> clause is given, it must say
      <literal>RETURNS record</>.
     </para>

     <para>
      When the return type of a <application>PL/pgSQL</application>
      function is declared as a polymorphic type (<type>anyelement</type>
      or <type>anyarray</type>), a special parameter <literal>$0</literal>
      is created.  Its data type is the actual return type of the function,
      as deduced from the actual input types (see <xref
      linkend="extend-types-polymorphic">).
      This allows the function to access its actual return type
      as shown in <xref linkend="plpgsql-declaration-type">.
      <literal>$0</literal> is initialized to null and can be modified by
      the function, so it can be used to hold the return value if desired,
      though that is not required.  <literal>$0</literal> can also be
      given an alias.  For example, this function works on any data type
      that has a <literal>+</> operator:

<programlisting>
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;
</programlisting>
     </para>

     <para>
      The same effect can be had by declaring one or more output parameters as