Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 6108 insertions, 0 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 0000000..5977534
--- /dev/null
+++ b/doc/src/sgml/plpgsql.sgml
@@ -0,0 +1,6108 @@
+<!-- doc/src/sgml/plpgsql.sgml -->
+
+<chapter id="plpgsql">
+  <title><application>PL/pgSQL</application> &mdash; <acronym>SQL</acronym> Procedural Language</title>
+
+ <indexterm zone="plpgsql">
+  <primary>PL/pgSQL</primary>
+ </indexterm>
+
+ <sect1 id="plpgsql-overview">
+  <title>Overview</title>
+
+ <para>
+  <application>PL/pgSQL</application> is a loadable procedural
+  language for the <productname>PostgreSQL</productname> database
+  system.  The design goals of <application>PL/pgSQL</application> were to create
+  a loadable procedural language that
+
+    <itemizedlist>
+     <listitem>
+      <para>
+       can be used to create functions, procedures, and triggers,
+      </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, procedures, 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>
+    Functions created with <application>PL/pgSQL</application> can be
+    used anywhere that built-in functions could be used.
+    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>
+
+   <para>
+    In <productname>PostgreSQL</productname> 9.0 and later,
+    <application>PL/pgSQL</application> is installed by default.
+    However it is still a loadable module, so especially security-conscious
+    administrators could choose to remove it.
+   </para>
+
+  <sect2 id="plpgsql-advantages">
+   <title>Advantages of Using <application>PL/pgSQL</application></title>
+
+    <para>
+     <acronym>SQL</acronym> is the language <productname>PostgreSQL</productname>
+     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 and
+     process the results, do some computation, then send further
+     queries to the server.  All this incurs interprocess
+     communication and will 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 with considerable
+     savings of client/server communication overhead.
+    </para>
+    <itemizedlist>
+
+     <listitem><para> Extra round trips between
+     client and server are eliminated </para></listitem>
+
+     <listitem><para> Intermediate results that the client does not
+     need do not have to be marshaled or transferred between server
+     and client </para></listitem>
+
+     <listitem><para> Multiple rounds of query
+     parsing can be avoided </para></listitem>
+
+    </itemizedlist>
+    <para> This can result in a considerable performance increase as
+    compared to an application that does not use stored functions.
+    </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 accepting <type>record</type>, which means that any
+     composite type will do as input, or
+     as returning <type>record</type>, 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</application> functions can be declared to accept a variable
+     number of arguments by using the <literal>VARIADIC</literal> marker.  This
+     works exactly the same way as for SQL functions, as discussed in
+     <xref linkend="xfunc-sql-variadic-functions"/>.
+    </para>
+
+    <para>
+     <application>PL/pgSQL</application> functions can also be declared to
+     accept and return the polymorphic types described in
+     <xref linkend="extend-types-polymorphic"/>, thus allowing the actual data
+     types handled by the function to vary from call to call.
+     Examples appear in <xref linkend="plpgsql-declaration-parameters"/>.
+    </para>
+
+    <para>
+     <application>PL/pgSQL</application> functions can also be declared to return
+     a <quote>set</quote> (or table) of any data type that can be returned as
+     a single instance.  Such a function generates its output by executing
+     <command>RETURN NEXT</command> for each desired element of the result
+     set, or by using <command>RETURN QUERY</command> to output the result of
+     evaluating a query.
+    </para>
+
+    <para>
+     Finally, a <application>PL/pgSQL</application> function can be declared to return
+     <type>void</type> if it has no useful return value.  (Alternatively, it
+     could be written as a procedure in that case.)
+    </para>
+
+    <para>
+     <application>PL/pgSQL</application> 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.
+     The <literal>RETURNS TABLE</literal> notation can also be used in place
+     of <literal>RETURNS SETOF</literal>.
+    </para>
+
+    <para>
+     Specific examples appear in
+     <xref linkend="plpgsql-declaration-parameters"/> and
+     <xref linkend="plpgsql-statements-returning"/>.
+    </para>
+  </sect2>
+ </sect1>
+
+ <sect1 id="plpgsql-structure">
+  <title>Structure of <application>PL/pgSQL</application></title>
+
+  <para>
+   Functions written in <application>PL/pgSQL</application> are defined
+   to the server by executing <xref linkend="sql-createfunction"/> commands.
+   Such a command would normally look like, say,
+<programlisting>
+CREATE FUNCTION somefunc(integer, text) RETURNS integer
+AS '<replaceable>function body text</replaceable>'
+LANGUAGE plpgsql;
+</programlisting>
+   The function body is simply a string literal so far as <command>CREATE
+   FUNCTION</command> is concerned.  It is often helpful to use dollar quoting
+   (see <xref linkend="sql-syntax-dollar-quoting"/>) to write the function
+   body, rather than the normal single quote syntax.  Without dollar quoting,
+   any single quotes or backslashes in the function body must be escaped by
+   doubling them.  Almost all the examples in this chapter use dollar-quoted
+   literals for their function bodies.
+  </para>
+
+  <para>
+   <application>PL/pgSQL</application> is a block-structured language.
+   The complete text of a function body must be a
+   <firstterm>block</firstterm>. 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>
+
+    <tip>
+     <para>
+      A common mistake is to write a semicolon immediately after
+      <literal>BEGIN</literal>.  This is incorrect and will result in a syntax error.
+     </para>
+    </tip>
+
+    <para>
+     A <replaceable>label</replaceable> is only needed if you want to
+     identify the block for use
+     in an <literal>EXIT</literal> statement, or to qualify the names of the
+     variables declared in the block.  If a label is given after
+     <literal>END</literal>, it must match the label at the block's beginning.
+    </para>
+
+    <para>
+     All key words are case-insensitive.
+     Identifiers are implicitly converted to lower case
+     unless double-quoted, just as they are in ordinary SQL commands.
+    </para>
+
+    <para>
+     Comments work the same way in <application>PL/pgSQL</application> code as in
+     ordinary SQL.  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 matching occurrence of
+     <literal>*/</literal>.  Block comments nest.
+    </para>
+
+    <para>
+     Any statement in the statement section of a block
+     can be a <firstterm>subblock</firstterm>.  Subblocks can be used for
+     logical grouping or to localize variables to a small group
+     of statements.  Variables declared in a subblock mask any
+     similarly-named variables of outer blocks for the duration
+     of the subblock; but you can access the outer variables anyway
+     if you qualify their names with their block's label. For example:
+<programlisting>
+CREATE FUNCTION somefunc() RETURNS integer AS $$
+&lt;&lt; outerblock &gt;&gt;
+DECLARE
+    quantity integer := 30;
+BEGIN
+    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
+    quantity := 50;
+    --
+    -- Create a subblock
+    --
+    DECLARE
+        quantity integer := 80;
+    BEGIN
+        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
+        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
+    END;
+
+    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50
+
+    RETURN quantity;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+    </para>
+
+    <note>
+     <para>
+      There is actually a hidden <quote>outer block</quote> surrounding the body
+      of any <application>PL/pgSQL</application> function.  This block provides the
+      declarations of the function's parameters (if any), as well as some
+      special variables such as <literal>FOUND</literal> (see
+      <xref linkend="plpgsql-statements-diagnostics"/>).  The outer block is
+      labeled with the function's name, meaning that parameters and special
+      variables can be qualified with the function's name.
+     </para>
+    </note>
+
+    <para>
+     It is important not to confuse the use of
+     <command>BEGIN</command>/<command>END</command> for grouping statements in
+     <application>PL/pgSQL</application> with the similarly-named SQL commands
+     for transaction
+     control.  <application>PL/pgSQL</application>'s <command>BEGIN</command>/<command>END</command>
+     are only for grouping; they do not start or end a transaction.
+     See <xref linkend="plpgsql-transactions"/> for information on managing
+     transactions in <application>PL/pgSQL</application>.
+     Also, a block containing an <literal>EXCEPTION</literal> 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 exceptions are that the loop variable of a <literal>FOR</literal> loop
+     iterating over a range of integer values is automatically declared as an
+     integer variable, and likewise the loop variable of a <literal>FOR</literal> loop
+     iterating over a cursor's result is automatically declared as a
+     record variable.)
+    </para>
+
+    <para>
+     <application>PL/pgSQL</application> 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> COLLATE <replaceable>collation_name</replaceable> </optional> <optional> NOT NULL </optional> <optional> { DEFAULT | := | = } <replaceable>expression</replaceable> </optional>;
+</synopsis>
+      The <literal>DEFAULT</literal> clause, if given, specifies the initial value assigned
+      to the variable when the block is entered.  If the <literal>DEFAULT</literal> clause
+      is not given then the variable is initialized to the
+      <acronym>SQL</acronym> null value.
+      The <literal>CONSTANT</literal> option prevents the variable from being
+      assigned to after initialization, so that its value will remain constant
+      for the duration of the block.
+      The <literal>COLLATE</literal> option specifies a collation to use for the
+      variable (see <xref linkend="plpgsql-declaration-collation"/>).
+      If <literal>NOT NULL</literal>
+      is specified, an assignment of a null value results in a run-time
+      error. All variables declared as <literal>NOT NULL</literal>
+      must have a nonnull default value specified.
+      Equal (<literal>=</literal>) can be used instead of PL/SQL-compliant
+      <literal>:=</literal>.
+     </para>
+
+     <para>
+      A variable's default value is evaluated and assigned to the variable
+      each time the block is entered (not just once per function call).
+      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';
+transaction_time CONSTANT timestamp with time zone := now();
+</programlisting>
+     </para>
+
+     <para>
+      Once declared, a variable's value can be used in later initialization
+      expressions in the same block, for example:
+<programlisting>
+DECLARE
+  x integer := 1;
+  y integer := x + 1;
+</programlisting>
+     </para>
+
+    <sect2 id="plpgsql-declaration-parameters">
+     <title>Declaring 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 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>
+     </para>
+
+    <note>
+     <para>
+      These two examples are not perfectly equivalent.  In the first case,
+      <literal>subtotal</literal> could be referenced as
+      <literal>sales_tax.subtotal</literal>, but in the second case it could not.
+      (Had we attached a label to the inner block, <literal>subtotal</literal> could
+      be qualified with that label, instead.)
+     </para>
+    </note>
+
+     <para>
+      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</literal> &mdash; we could have
+      included it, but it would be redundant.
+     </para>
+
+     <para>
+      To call a function with <literal>OUT</literal> parameters, omit the
+      output parameter(s) in the function call:
+<programlisting>
+SELECT sales_tax(100.00);
+</programlisting>
+     </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;
+
+SELECT * FROM sum_n_product(2, 4);
+ sum | prod
+-----+------
+   6 |    8
+</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</literal> clause is given, it must say
+      <literal>RETURNS record</literal>.
+     </para>
+
+     <para>
+      This also works with procedures, for example:
+
+<programlisting>
+CREATE PROCEDURE 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>
+
+      In a call to a procedure, all the parameters must be specified.  For
+      output parameters, <literal>NULL</literal> may be specified when
+      calling the procedure from plain SQL:
+<programlisting>
+CALL sum_n_product(2, 4, NULL, NULL);
+ sum | prod
+-----+------
+   6 |    8
+</programlisting>
+
+      However, when calling a procedure
+      from <application>PL/pgSQL</application>, you should instead write a
+      variable for any output parameter; the variable will receive the result
+      of the call.  See <xref linkend="plpgsql-statements-calling-procedure"/>
+      for details.
+     </para>
+
+     <para>
+      Another way to declare a <application>PL/pgSQL</application> function
+      is with <literal>RETURNS TABLE</literal>, for example:
+
+<programlisting>
+CREATE FUNCTION extended_sales(p_itemno int)
+RETURNS TABLE(quantity int, total numeric) AS $$
+BEGIN
+    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
+                 WHERE s.itemno = p_itemno;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+      This is exactly equivalent to declaring one or more <literal>OUT</literal>
+      parameters and specifying <literal>RETURNS SETOF
+      <replaceable>sometype</replaceable></literal>.
+     </para>
+
+     <para>
+      When the return type of a <application>PL/pgSQL</application> function
+      is declared as a polymorphic type (see
+      <xref linkend="extend-types-polymorphic"/>), 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.
+      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>+</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 obtained by declaring one or more output parameters as
+      polymorphic types.  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>
+
+     <para>
+      In practice it might be more useful to declare a polymorphic function
+      using the <type>anycompatible</type> family of types, so that automatic
+      promotion of the input arguments to a common type will occur.
+      For example:
+
+<programlisting>
+CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
+RETURNS anycompatible AS $$
+BEGIN
+    RETURN v1 + v2 + v3;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+      With this example, a call such as
+
+<programlisting>
+SELECT add_three_values(1, 2, 4.7);
+</programlisting>
+
+      will work, automatically promoting the integer inputs to numeric.
+      The function using <type>anyelement</type> would require you to
+      cast the three inputs to the same type manually.
+     </para>
+    </sect2>
+
+  <sect2 id="plpgsql-declaration-alias">
+   <title><literal>ALIAS</literal></title>
+
+<synopsis>
+<replaceable>newname</replaceable> ALIAS FOR <replaceable>oldname</replaceable>;
+</synopsis>
+
+   <para>
+    The <literal>ALIAS</literal> syntax is more general than is suggested in the
+    previous section: you can declare an alias for any variable, not just
+    function parameters.  The main practical use for this is to assign
+    a different name for variables with predetermined names, such as
+    <varname>NEW</varname> or <varname>OLD</varname> within
+    a trigger function.
+   </para>
+
+   <para>
+    Examples:
+<programlisting>
+DECLARE
+  prior ALIAS FOR old;
+  updated ALIAS FOR new;
+</programlisting>
+   </para>
+
+   <para>
+    Since <literal>ALIAS</literal> creates two different ways to name the same
+    object, unrestricted use can be confusing.  It's best to use it only
+    for the purpose of overriding predetermined names.
+   </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</literal> 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</literal>
+    from <type>integer</type> to <type>real</type>), you might 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 can
+    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</firstterm>
+    variable (or <firstterm>row-type</firstterm> variable).  Such a variable
+    can hold a whole row of a <command>SELECT</command> or <command>FOR</command>
+    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</productname> 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></literal> will be a row variable, and fields can
+    be selected from it, for example <literal>$1.user_id</literal>.
+   </para>
+
+   <para>
+    Here is an example of using composite types.  <structname>table1</structname>
+    and <structname>table2</structname> 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</command> or <command>FOR</command> 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</literal> 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</type>, this is not quite the
+    same concept as a record variable, even though such a function might
+    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</type> 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-collation">
+   <title>Collation of <application>PL/pgSQL</application> Variables</title>
+
+   <indexterm>
+    <primary>collation</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <para>
+    When a <application>PL/pgSQL</application> function has one or more
+    parameters of collatable data types, a collation is identified for each
+    function call depending on the collations assigned to the actual
+    arguments, as described in <xref linkend="collation"/>.  If a collation is
+    successfully identified (i.e., there are no conflicts of implicit
+    collations among the arguments) then all the collatable parameters are
+    treated as having that collation implicitly.  This will affect the
+    behavior of collation-sensitive operations within the function.
+    For example, consider
+
+<programlisting>
+CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
+BEGIN
+    RETURN a &lt; b;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT less_than(text_field_1, text_field_2) FROM table1;
+SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;
+</programlisting>
+
+    The first use of <function>less_than</function> will use the common collation
+    of <structfield>text_field_1</structfield> and <structfield>text_field_2</structfield> for
+    the comparison, while the second use will use <literal>C</literal> collation.
+   </para>
+
+   <para>
+    Furthermore, the identified collation is also assumed as the collation of
+    any local variables that are of collatable types.  Thus this function
+    would not work any differently if it were written as
+
+<programlisting>
+CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
+DECLARE
+    local_a text := a;
+    local_b text := b;
+BEGIN
+    RETURN local_a &lt; local_b;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+   </para>
+
+   <para>
+    If there are no parameters of collatable data types, or no common
+    collation can be identified for them, then parameters and local variables
+    use the default collation of their data type (which is usually the
+    database's default collation, but could be different for variables of
+    domain types).
+   </para>
+
+   <para>
+    A local variable of a collatable data type can have a different collation
+    associated with it by including the <literal>COLLATE</literal> option in its
+    declaration, for example
+
+<programlisting>
+DECLARE
+    local_a text COLLATE "en_US";
+</programlisting>
+
+    This option overrides the collation that would otherwise be
+    given to the variable according to the rules above.
+   </para>
+
+   <para>
+    Also, of course explicit <literal>COLLATE</literal> clauses can be written inside
+    a function if it is desired to force a particular collation to be used in
+    a particular operation.  For example,
+
+<programlisting>
+CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
+BEGIN
+    RETURN a &lt; b COLLATE "C";
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+    This overrides the collations associated with the table columns,
+    parameters, or local variables used in the expression, just as would
+    happen in a plain SQL command.
+   </para>
+  </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 main
+     <acronym>SQL</acronym> executor.  For example, when you write
+     a <application>PL/pgSQL</application> statement like
+<synopsis>
+IF <replaceable>expression</replaceable> THEN ...
+</synopsis>
+     <application>PL/pgSQL</application> will evaluate the expression by
+     feeding a query like
+<synopsis>
+SELECT <replaceable>expression</replaceable>
+</synopsis>
+     to the main SQL engine.  While forming the <command>SELECT</command> command,
+     any occurrences of <application>PL/pgSQL</application> variable names
+     are replaced by query parameters, as discussed in detail in
+     <xref linkend="plpgsql-var-subst"/>.
+     This allows the query plan for the <command>SELECT</command> to
+     be prepared just once and then reused for subsequent
+     evaluations with different values of the variables.  Thus, what
+     really happens on first use of an expression is essentially a
+     <command>PREPARE</command> command.  For example, if we have declared
+     two integer variables <literal>x</literal> and <literal>y</literal>, and we write
+<programlisting>
+IF x &lt; y THEN ...
+</programlisting>
+     what happens behind the scenes is equivalent to
+<programlisting>
+PREPARE <replaceable>statement_name</replaceable>(integer, integer) AS SELECT $1 &lt; $2;
+</programlisting>
+     and then this prepared statement is <command>EXECUTE</command>d for each
+     execution of the <command>IF</command> statement, with the current values
+     of the <application>PL/pgSQL</application> variables supplied as
+     parameter values.  Normally these details are
+     not important to a <application>PL/pgSQL</application> user, but
+     they are useful to know when trying to diagnose a problem.
+     More information appears in <xref linkend="plpgsql-plan-caching"/>.
+    </para>
+
+    <para>
+     Since an <replaceable>expression</replaceable> is converted to a
+     <literal>SELECT</literal> command, it can contain the same clauses
+     that an ordinary <literal>SELECT</literal> would, except that it
+     cannot include a top-level <literal>UNION</literal>,
+     <literal>INTERSECT</literal>, or <literal>EXCEPT</literal> clause.
+     Thus for example one could test whether a table is non-empty with
+<programlisting>
+IF count(*) &gt; 0 FROM my_table THEN ...
+</programlisting>
+     since the <replaceable>expression</replaceable>
+     between <literal>IF</literal> and <literal>THEN</literal> is parsed as
+     though it were <literal>SELECT count(*) &gt; 0 FROM my_table</literal>.
+     The <literal>SELECT</literal> must produce a single column, and not
+     more than one row.  (If it produces no rows, the result is taken as
+     NULL.)
+    </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,
+    as described in <xref linkend="plpgsql-statements-general-sql"/>.
+   </para>
+
+   <sect2 id="plpgsql-statements-assignment">
+    <title>Assignment</title>
+
+    <para>
+     An assignment of a value to a <application>PL/pgSQL</application>
+     variable is written as:
+<synopsis>
+<replaceable>variable</replaceable> { := | = } <replaceable>expression</replaceable>;
+</synopsis>
+     As explained previously, the expression in such a statement is evaluated
+     by means of an SQL <command>SELECT</command> command sent to the main
+     database engine.  The expression must yield a single value (possibly
+     a row value, if the variable is a row or record variable).  The target
+     variable can be a simple variable (optionally qualified with a block
+     name), a field of a row or record target, or an element or slice of
+     an array target.  Equal (<literal>=</literal>) can be
+     used instead of PL/SQL-compliant <literal>:=</literal>.
+    </para>
+
+    <para>
+     If the expression's result data type doesn't match the variable's
+     data type, the value will be coerced as though by an assignment cast
+     (see <xref linkend="typeconv-query"/>).  If no assignment cast is known
+     for the pair of data types involved, the <application>PL/pgSQL</application>
+     interpreter will attempt to convert the result value textually, that is
+     by applying the result type's output function followed by the variable
+     type's input function.  Note that this could 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>
+tax := subtotal * 0.06;
+my_record.user_id := 20;
+my_array[j] := 20;
+my_array[1:3] := array[1,2,3];
+complex_array[n].realpart = 12.3;
+</programlisting>
+    </para>
+   </sect2>
+
+   <sect2 id="plpgsql-statements-general-sql">
+    <title>Executing SQL Commands</title>
+
+    <para>
+     In general, any SQL command that does not return rows can be executed
+     within a <application>PL/pgSQL</application> function just by writing
+     the command.  For example, you could create and fill a table by writing
+<programlisting>
+CREATE TABLE mytable (id int primary key, data text);
+INSERT INTO mytable VALUES (1,'one'), (2,'two');
+</programlisting>
+    </para>
+
+    <para>
+     If the command does return rows (for example <command>SELECT</command>,
+     or <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>
+     with <literal>RETURNING</literal>), there are two ways to proceed.
+     When the command will return at most one row, or you only care about
+     the first row of output, write the command as usual but add
+     an <literal>INTO</literal> clause to capture the output, as described
+     in <xref linkend="plpgsql-statements-sql-onerow"/>.
+     To process all of the output rows, write the command as the data
+     source for a <command>FOR</command> loop, as described in
+     <xref linkend="plpgsql-records-iterating"/>.
+    </para>
+
+    <para>
+     Usually it is not sufficient just to execute statically-defined SQL
+     commands.  Typically you'll want a command to use varying data values,
+     or even to vary in more fundamental ways such as by using different
+     table names at different times.  Again, there are two ways to proceed
+     depending on the situation.
+    </para>
+
+    <para>
+     <application>PL/pgSQL</application> variable values can be
+     automatically inserted into optimizable SQL commands, which
+     are <command>SELECT</command>, <command>INSERT</command>,
+     <command>UPDATE</command>, <command>DELETE</command>,
+     <command>MERGE</command>, and certain
+     utility commands that incorporate one of these, such
+     as <command>EXPLAIN</command> and <command>CREATE TABLE ... AS
+     SELECT</command>.  In these commands,
+     any <application>PL/pgSQL</application> variable name appearing
+     in the command text is replaced by a query parameter, and then the
+     current value of the variable is provided as the parameter value
+     at run time.  This is exactly like the processing described earlier
+     for expressions; for details see <xref linkend="plpgsql-var-subst"/>.
+    </para>
+
+    <para>
+     When executing an optimizable SQL command in this way,
+     <application>PL/pgSQL</application> may cache and re-use the execution
+     plan for the command, as discussed in
+     <xref linkend="plpgsql-plan-caching"/>.
+    </para>
+
+    <para>
+     Non-optimizable SQL commands (also called utility commands) are not
+     capable of accepting query parameters.  So automatic substitution
+     of <application>PL/pgSQL</application> variables does not work in such
+     commands.  To include non-constant text in a utility command executed
+     from <application>PL/pgSQL</application>, you must build the utility
+     command as a string and then <command>EXECUTE</command> it, as
+     discussed in <xref linkend="plpgsql-statements-executing-dyn"/>.
+    </para>
+
+    <para>
+     <command>EXECUTE</command> must also be used if you want to modify
+     the command in some other way than supplying a data value, for example
+     by changing a table name.
+    </para>
+
+    <para>
+     Sometimes it is useful to evaluate an expression or <command>SELECT</command>
+     query but discard the result, for example when calling a function
+     that has 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> and discards the
+     result.  Write the <replaceable>query</replaceable> the same
+     way you would write an SQL <command>SELECT</command> command, but replace the
+     initial keyword <command>SELECT</command> with <command>PERFORM</command>.
+     For <command>WITH</command> queries, use <command>PERFORM</command> and then
+     place the query in parentheses.  (In this case, the query can only
+     return one row.)
+     <application>PL/pgSQL</application> variables will be
+     substituted into the query just as described above,
+     and the plan is cached in the same way.  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 (see
+     <xref linkend="plpgsql-statements-diagnostics"/>).
+    </para>
+
+    <note>
+     <para>
+      One might expect that writing <command>SELECT</command> directly
+      would accomplish this result, but at
+      present the only accepted way to do it is
+      <command>PERFORM</command>.  An SQL command that can return rows,
+      such as <command>SELECT</command>, will be rejected as an error
+      unless it has an <literal>INTO</literal> clause as discussed in the
+      next section.
+     </para>
+    </note>
+
+    <para>
+     An example:
+<programlisting>
+PERFORM create_mv('cs_session_page_requests_mv', my_query);
+</programlisting>
+    </para>
+   </sect2>
+
+   <sect2 id="plpgsql-statements-sql-onerow">
+    <title>Executing a Command with a Single-Row Result</title>
+
+    <indexterm zone="plpgsql-statements-sql-onerow">
+     <primary>SELECT INTO</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+
+    <indexterm zone="plpgsql-statements-sql-onerow">
+     <primary>RETURNING INTO</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+
+    <para>
+     The result of an SQL command yielding a single row (possibly of multiple
+     columns) can be assigned to a record variable, row-type variable, or list
+     of scalar variables.  This is done by writing the base SQL command and
+     adding an <literal>INTO</literal> clause.  For example,
+
+<synopsis>
+SELECT <replaceable>select_expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable> FROM ...;
+INSERT ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
+UPDATE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
+DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
+</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.
+     <application>PL/pgSQL</application> variables will be
+     substituted into the rest of the command (that is, everything but the
+     <literal>INTO</literal> clause) just as described above,
+     and the plan is cached in the same way.
+     This works for <command>SELECT</command>,
+     <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> with
+     <literal>RETURNING</literal>, and certain utility commands
+     that return row sets, such as <command>EXPLAIN</command>.
+     Except for the <literal>INTO</literal> clause, the SQL command is the same
+     as it would be written outside <application>PL/pgSQL</application>.
+    </para>
+
+   <tip>
+    <para>
+     Note that this interpretation of <command>SELECT</command> with <literal>INTO</literal>
+     is quite different from <productname>PostgreSQL</productname>'s regular
+     <command>SELECT INTO</command> command, wherein the <literal>INTO</literal>
+     target is a newly created table.  If you want to create a table from a
+     <command>SELECT</command> result inside a
+     <application>PL/pgSQL</application> function, use the syntax
+     <command>CREATE TABLE ... AS SELECT</command>.
+    </para>
+   </tip>
+
+    <para>
+     If a row variable or a variable list is used as target,
+     the command's result columns
+     must exactly match the structure of the target as to number and data
+     types, or else a run-time error
+     occurs.  When a record variable is the target, it automatically
+     configures itself to the row type of the command's result columns.
+    </para>
+
+    <para>
+     The <literal>INTO</literal> clause can appear almost anywhere in the SQL
+     command.  Customarily it is written either just before or just after
+     the list of <replaceable>select_expressions</replaceable> in a
+     <command>SELECT</command> command, or at the end of the command for other
+     command types.  It is recommended that you follow this convention
+     in case the <application>PL/pgSQL</application> parser becomes
+     stricter in future versions.
+    </para>
+
+    <para>
+     If <literal>STRICT</literal> is not specified in the <literal>INTO</literal>
+     clause, then <replaceable>target</replaceable> will be set to the first
+     row returned by the command, or to nulls if the command returned no rows.
+     (Note that <quote>the first row</quote> is not
+     well-defined unless you've used <literal>ORDER BY</literal>.)  Any result rows
+     after the first row are discarded.
+     You can check the special <literal>FOUND</literal> variable (see
+     <xref linkend="plpgsql-statements-diagnostics"/>) to
+     determine whether a row was returned:
+
+<programlisting>
+SELECT * INTO myrec FROM emp WHERE empname = myname;
+IF NOT FOUND THEN
+    RAISE EXCEPTION 'employee % not found', myname;
+END IF;
+</programlisting>
+
+     If the <literal>STRICT</literal> option is specified, the command must
+     return exactly one row or a run-time error will be reported, either
+     <literal>NO_DATA_FOUND</literal> (no rows) or <literal>TOO_MANY_ROWS</literal>
+     (more than one row). You can use an exception block if you wish
+     to catch the error, for example:
+
+<programlisting>
+BEGIN
+    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
+    EXCEPTION
+        WHEN NO_DATA_FOUND THEN
+            RAISE EXCEPTION 'employee % not found', myname;
+        WHEN TOO_MANY_ROWS THEN
+            RAISE EXCEPTION 'employee % not unique', myname;
+END;
+</programlisting>
+     Successful execution of a command with <literal>STRICT</literal>
+     always sets <literal>FOUND</literal> to true.
+    </para>
+
+    <para>
+     For <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> with
+     <literal>RETURNING</literal>, <application>PL/pgSQL</application> reports
+     an error for more than one returned row, even when
+     <literal>STRICT</literal> is not specified.  This is because there
+     is no option such as <literal>ORDER BY</literal> with which to determine
+     which affected row should be returned.
+    </para>
+
+    <para>
+     If <literal>print_strict_params</literal> is enabled for the function,
+     then when an error is thrown because the requirements
+     of <literal>STRICT</literal> are not met, the <literal>DETAIL</literal> part of
+     the error message will include information about the parameters
+     passed to the command.
+     You can change the <literal>print_strict_params</literal>
+     setting for all functions by setting
+     <varname>plpgsql.print_strict_params</varname>, though only subsequent
+     function compilations will be affected.  You can also enable it
+     on a per-function basis by using a compiler option, for example:
+<programlisting>
+CREATE FUNCTION get_userid(username text) RETURNS int
+AS $$
+#print_strict_params on
+DECLARE
+userid int;
+BEGIN
+    SELECT users.userid INTO STRICT userid
+        FROM users WHERE users.username = get_userid.username;
+    RETURN userid;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+     On failure, this function might produce an error message such as
+<programlisting>
+ERROR:  query returned no rows
+DETAIL:  parameters: $1 = 'nosuchuser'
+CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement
+</programlisting>
+    </para>
+
+    <note>
+     <para>
+      The <literal>STRICT</literal> option matches the behavior of
+      Oracle PL/SQL's <command>SELECT INTO</command> and related statements.
+     </para>
+    </note>
+
+   </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 (as discussed in
+     <xref linkend="plpgsql-plan-caching"/>) 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> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
+</synopsis>
+
+     where <replaceable>command-string</replaceable> is an expression
+     yielding a string (of type <type>text</type>) containing the
+     command to be executed.  The optional <replaceable>target</replaceable>
+     is a record variable, a row variable, or a comma-separated list of
+     simple variables and record/row fields, into which the results of
+     the command will be stored.  The optional <literal>USING</literal> expressions
+     supply values to be inserted into the command.
+    </para>
+
+    <para>
+     No substitution of <application>PL/pgSQL</application> variables is done on the
+     computed command string.  Any required variable values must be inserted
+     in the command string as it is constructed; or you can use parameters
+     as described below.
+    </para>
+
+    <para>
+     Also, there is no plan caching for commands executed via
+     <command>EXECUTE</command>.  Instead, the command is always planned
+     each time the statement is run. Thus the command
+     string can be dynamically created within the function to perform
+     actions on different tables and columns.
+    </para>
+
+    <para>
+     The <literal>INTO</literal> clause specifies where the results of
+     an SQL command returning rows should be assigned. If a row variable
+     or variable list is provided, it must exactly match the structure
+     of the command's results; if a
+     record variable is provided, it will configure itself to match the
+     result structure automatically. If multiple rows are returned,
+     only the first will be assigned to the <literal>INTO</literal>
+     variable(s). If no rows are returned, NULL is assigned to the
+     <literal>INTO</literal> variable(s). If no <literal>INTO</literal>
+     clause is specified, the command results are discarded.
+    </para>
+
+    <para>
+     If the <literal>STRICT</literal> option is given, an error is reported
+     unless the command produces exactly one row.
+    </para>
+
+    <para>
+     The command string can use parameter values, which are referenced
+     in the command as <literal>$1</literal>, <literal>$2</literal>, etc.
+     These symbols refer to values supplied in the <literal>USING</literal>
+     clause.  This method is often preferable to inserting data values
+     into the command string as text: it avoids run-time overhead of
+     converting the values to text and back, and it is much less prone
+     to SQL-injection attacks since there is no need for quoting or escaping.
+     An example is:
+<programlisting>
+EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted &lt;= $2'
+   INTO c
+   USING checked_user, checked_date;
+</programlisting>
+    </para>
+
+    <para>
+     Note that parameter symbols can only be used for data values
+     &mdash; if you want to use dynamically determined table or column
+     names, you must insert them into the command string textually.
+     For example, if the preceding query needed to be done against a
+     dynamically selected table, you could do this:
+<programlisting>
+EXECUTE 'SELECT count(*) FROM '
+    || quote_ident(tabname)
+    || ' WHERE inserted_by = $1 AND inserted &lt;= $2'
+   INTO c
+   USING checked_user, checked_date;
+</programlisting>
+     A cleaner approach is to use <function>format()</function>'s <literal>%I</literal>
+     specification to insert table or column names with automatic quoting:
+<programlisting>
+EXECUTE format('SELECT count(*) FROM %I '
+   'WHERE inserted_by = $1 AND inserted &lt;= $2', tabname)
+   INTO c
+   USING checked_user, checked_date;
+</programlisting>
+     (This example relies on the SQL rule that string literals separated by a
+     newline are implicitly concatenated.)
+    </para>
+
+    <para>
+     Another restriction on parameter symbols is that they only work in
+     optimizable SQL commands
+     (<command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
+     <command>DELETE</command>, <command>MERGE</command>, and certain commands containing one of these).
+     In other statement
+     types (generically called utility statements), you must insert
+     values textually even if they are just data values.
+    </para>
+
+    <para>
+     An <command>EXECUTE</command> with a simple constant command string and some
+     <literal>USING</literal> parameters, as in the first example above, is
+     functionally equivalent to just writing the command directly in
+     <application>PL/pgSQL</application> and allowing replacement of
+     <application>PL/pgSQL</application> variables to happen automatically.
+     The important difference is that <command>EXECUTE</command> will re-plan
+     the command on each execution, generating a plan that is specific
+     to the current parameter values; whereas
+     <application>PL/pgSQL</application> may otherwise create a generic plan
+     and cache it for re-use.  In situations where the best plan depends
+     strongly on the parameter values, it can be helpful to use
+     <command>EXECUTE</command> to positively ensure that a generic plan is not
+     selected.
+    </para>
+
+    <para>
+     <command>SELECT INTO</command> is not currently supported within
+     <command>EXECUTE</command>; instead, execute a plain <command>SELECT</command>
+     command and specify <literal>INTO</literal> as part of the <command>EXECUTE</command>
+     itself.
+    </para>
+
+   <note>
+    <para>
+     The <application>PL/pgSQL</application>
+     <command>EXECUTE</command> statement is not related to the
+     <link linkend="sql-execute"><command>EXECUTE</command></link> SQL
+     statement supported by the
+     <productname>PostgreSQL</productname> server. The server's
+     <command>EXECUTE</command> statement cannot be used directly within
+     <application>PL/pgSQL</application> functions (and is not needed).
+    </para>
+   </note>
+
+   <example id="plpgsql-quote-literal-example">
+   <title>Quoting Values in Dynamic Queries</title>
+
+    <indexterm>
+     <primary>quote_ident</primary>
+     <secondary>use in PL/pgSQL</secondary>
+    </indexterm>
+
+    <indexterm>
+     <primary>quote_literal</primary>
+     <secondary>use in PL/pgSQL</secondary>
+    </indexterm>
+
+    <indexterm>
+     <primary>quote_nullable</primary>
+     <secondary>use in PL/pgSQL</secondary>
+    </indexterm>
+
+    <indexterm>
+     <primary>format</primary>
+     <secondary>use in PL/pgSQL</secondary>
+    </indexterm>
+
+    <para>
+     When working with dynamic commands you will often have to handle escaping
+     of single quotes.  The recommended method for quoting fixed text in your
+     function body is dollar quoting.  (If you have legacy code that does
+     not use dollar quoting, please refer to the
+     overview in <xref linkend="plpgsql-quote-tips"/>, which can save you
+     some effort when translating said code to a more reasonable scheme.)
+    </para>
+
+    <para>
+     Dynamic values require careful handling since they might contain
+     quote characters.
+     An example using <function>format()</function> (this assumes that you are
+     dollar quoting the function body so quote marks need not be doubled):
+<programlisting>
+EXECUTE format('UPDATE tbl SET %I = $1 '
+   'WHERE key = $2', colname) USING newvalue, keyvalue;
+</programlisting>
+     It is also possible to call the quoting functions directly:
+<programlisting>
+EXECUTE 'UPDATE tbl SET '
+        || quote_ident(colname)
+        || ' = '
+        || quote_literal(newvalue)
+        || ' WHERE key = '
+        || quote_literal(keyvalue);
+</programlisting>
+    </para>
+
+    <para>
+     This example demonstrates the use of the
+     <function>quote_ident</function> and
+     <function>quote_literal</function> functions (see <xref
+     linkend="functions-string"/>).  For safety, expressions containing column
+     or table identifiers should be passed through
+     <function>quote_ident</function> before insertion in a dynamic query.
+     Expressions containing values that should be literal strings in the
+     constructed command should be passed through <function>quote_literal</function>.
+     These functions 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>
+     Because <function>quote_literal</function> is labeled
+     <literal>STRICT</literal>, it will always return null when called with a
+     null argument.  In the above example, if <literal>newvalue</literal> or
+     <literal>keyvalue</literal> were null, the entire dynamic query string would
+     become null, leading to an error from <command>EXECUTE</command>.
+     You can avoid this problem by using the <function>quote_nullable</function>
+     function, which works the same as <function>quote_literal</function> except that
+     when called with a null argument it returns the string <literal>NULL</literal>.
+     For example,
+<programlisting>
+EXECUTE 'UPDATE tbl SET '
+        || quote_ident(colname)
+        || ' = '
+        || quote_nullable(newvalue)
+        || ' WHERE key = '
+        || quote_nullable(keyvalue);
+</programlisting>
+     If you are dealing with values that might be null, you should usually
+     use <function>quote_nullable</function> in place of <function>quote_literal</function>.
+    </para>
+
+    <para>
+     As always, care must be taken to ensure that null values in a query do
+     not deliver unintended results.  For example the <literal>WHERE</literal> clause
+<programlisting>
+'WHERE key = ' || quote_nullable(keyvalue)
+</programlisting>
+     will never succeed if <literal>keyvalue</literal> is null, because the
+     result of using the equality operator <literal>=</literal> with a null operand
+     is always null.  If you wish null to work like an ordinary key value,
+     you would need to rewrite the above as
+<programlisting>
+'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
+</programlisting>
+     (At present, <literal>IS NOT DISTINCT FROM</literal> is handled much less
+     efficiently than <literal>=</literal>, so don't do this unless you must.
+     See <xref linkend="functions-comparison"/> for
+     more information on nulls and <literal>IS DISTINCT</literal>.)
+    </para>
+
+    <para>
+     Note that dollar quoting is only useful for quoting fixed text.
+     It would be a very bad idea to try to write this example as:
+<programlisting>
+EXECUTE 'UPDATE tbl SET '
+        || quote_ident(colname)
+        || ' = $$'
+        || newvalue
+        || '$$ WHERE key = '
+        || quote_literal(keyvalue);
+</programlisting>
+     because it would break if the contents of <literal>newvalue</literal>
+     happened to contain <literal>$$</literal>.  The same objection would
+     apply to any other dollar-quoting delimiter you might pick.
+     So, to safely quote text that is not known in advance, you
+     <emphasis>must</emphasis> use <function>quote_literal</function>,
+     <function>quote_nullable</function>, or <function>quote_ident</function>, as appropriate.
+    </para>
+
+    <para>
+     Dynamic SQL statements can also be safely constructed using the
+     <function>format</function> function (see <xref
+     linkend="functions-string-format"/>). For example:
+<programlisting>
+EXECUTE format('UPDATE tbl SET %I = %L '
+   'WHERE key = %L', colname, newvalue, keyvalue);
+</programlisting>
+     <literal>%I</literal> is equivalent to <function>quote_ident</function>, and
+     <literal>%L</literal> is equivalent to <function>quote_nullable</function>.
+     The <function>format</function> function can be used in conjunction with
+     the <literal>USING</literal> clause:
+<programlisting>
+EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
+   USING newvalue, keyvalue;
+</programlisting>
+     This form is better because the variables are handled in their native
+     data type format, rather than unconditionally converting them to
+     text and quoting them via <literal>%L</literal>.  It is also more efficient.
+    </para>
+   </example>
+
+    <para>
+     A much larger example of a dynamic command and
+     <command>EXECUTE</command> can be seen in <xref
+     linkend="plpgsql-porting-ex2"/>, which builds and executes a
+     <command>CREATE FUNCTION</command> command to define a new function.
+    </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 <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replaceable>item</replaceable> <optional> , ... </optional>;
+</synopsis>
+
+     This command allows retrieval of system status indicators.
+     <literal>CURRENT</literal> is a noise word (but see also <command>GET STACKED
+     DIAGNOSTICS</command> in <xref linkend="plpgsql-exception-diagnostics"/>).
+     Each <replaceable>item</replaceable> is a key word identifying a status
+     value to be assigned to the specified <replaceable>variable</replaceable>
+     (which should be of the right data type to receive it).  The currently
+     available status items are shown
+     in <xref linkend="plpgsql-current-diagnostics-values"/>.  Colon-equal
+     (<literal>:=</literal>) can be used instead of the SQL-standard <literal>=</literal>
+     token.  An example:
+<programlisting>
+GET DIAGNOSTICS integer_var = ROW_COUNT;
+</programlisting>
+    </para>
+
+     <table id="plpgsql-current-diagnostics-values">
+      <title>Available Diagnostics Items</title>
+      <tgroup cols="3">
+       <colspec colname="col1" colwidth="1*"/>
+       <colspec colname="col2" colwidth="1*"/>
+       <colspec colname="col3" colwidth="2*"/>
+       <thead>
+        <row>
+         <entry>Name</entry>
+         <entry>Type</entry>
+         <entry>Description</entry>
+        </row>
+       </thead>
+       <tbody>
+        <row>
+         <entry><varname>ROW_COUNT</varname></entry>
+         <entry><type>bigint</type></entry>
+         <entry>the number of rows processed by the most
+          recent <acronym>SQL</acronym> command</entry>
+        </row>
+        <row>
+         <entry><literal>PG_CONTEXT</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>line(s) of text describing the current call stack
+          (see <xref linkend="plpgsql-call-stack"/>)</entry>
+        </row>
+        <row>
+         <entry><literal>PG_ROUTINE_OID</literal></entry>
+         <entry><type>oid</type></entry>
+         <entry>OID of the current function</entry>
+        </row>
+       </tbody>
+      </tgroup>
+     </table>
+
+    <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 a row is assigned, false if no
+            row is returned.
+           </para>
+          </listitem>
+          <listitem>
+           <para>
+            A <command>PERFORM</command> statement sets <literal>FOUND</literal>
+            true if it produces (and discards) one or more rows, false if
+            no row is produced.
+           </para>
+          </listitem>
+          <listitem>
+           <para>
+            <command>UPDATE</command>, <command>INSERT</command>, <command>DELETE</command>,
+            and <command>MERGE</command>
+            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</command> statement sets <literal>FOUND</literal>
+            true if it returns a row, false if no row is returned.
+           </para>
+          </listitem>
+          <listitem>
+           <para>
+            A <command>MOVE</command> statement sets <literal>FOUND</literal>
+            true if it successfully repositions the cursor, false otherwise.
+           </para>
+          </listitem>
+          <listitem>
+           <para>
+            A <command>FOR</command> or <command>FOREACH</command> statement sets
+            <literal>FOUND</literal> true
+            if it iterates one or more times, else false.
+            <literal>FOUND</literal> is set this way when the
+            loop exits; inside the execution of the loop,
+            <literal>FOUND</literal> is not modified by the
+            loop statement, although it might be changed by the
+            execution of other statements within the loop body.
+           </para>
+          </listitem>
+          <listitem>
+           <para>
+            <command>RETURN QUERY</command> and <command>RETURN QUERY
+            EXECUTE</command> statements set <literal>FOUND</literal>
+            true if the query returns at least one row, false if no row
+            is returned.
+           </para>
+          </listitem>
+         </itemizedlist>
+
+     Other <application>PL/pgSQL</application> statements do not change
+     the state of <literal>FOUND</literal>.
+     Note in particular that <command>EXECUTE</command>
+     changes the output of <command>GET DIAGNOSTICS</command>, but
+     does not change <literal>FOUND</literal>.
+    </para>
+
+    <para>
+     <literal>FOUND</literal> is a local variable within each
+     <application>PL/pgSQL</application> function; any changes to it
+     affect only the current function.
+    </para>
+
+   </sect2>
+
+   <sect2 id="plpgsql-statements-null">
+    <title>Doing Nothing At All</title>
+
+    <para>
+     Sometimes a placeholder statement that does nothing is useful.
+     For example, it can indicate that one arm of an if/then/else
+     chain is deliberately empty.  For this purpose, use the
+     <command>NULL</command> statement:
+
+<synopsis>
+NULL;
+</synopsis>
+    </para>
+
+    <para>
+     For example, the following two fragments of code are equivalent:
+<programlisting>
+BEGIN
+    y := x / 0;
+EXCEPTION
+    WHEN division_by_zero THEN
+        NULL;  -- ignore the error
+END;
+</programlisting>
+
+<programlisting>
+BEGIN
+    y := x / 0;
+EXCEPTION
+    WHEN division_by_zero THEN  -- ignore the error
+END;
+</programlisting>
+     Which is preferable is a matter of taste.
+    </para>
+
+    <note>
+     <para>
+      In Oracle's PL/SQL, empty statement lists are not allowed, and so
+      <command>NULL</command> statements are <emphasis>required</emphasis> for situations
+      such as this.  <application>PL/pgSQL</application> allows you to
+      just write nothing, instead.
+     </para>
+    </note>
+
+   </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</application>. With
+    <application>PL/pgSQL</application>'s control structures,
+    you can manipulate <productname>PostgreSQL</productname> 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 id="plpgsql-statements-returning-return">
+     <title><command>RETURN</command></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 used for <application>PL/pgSQL</application> functions that do
+      not return a set.
+     </para>
+
+     <para>
+      In a function that returns a scalar type, the expression's result will
+      automatically be cast into the function's return type as described for
+      assignments.  But to return a composite (row) value, you must write an
+      expression delivering exactly the requested column set.  This may
+      require use of explicit casting.
+     </para>
+
+     <para>
+      If you declared the function with output parameters, write just
+      <command>RETURN</command> with no expression.  The current values
+      of the output parameter variables will be returned.
+     </para>
+
+     <para>
+      If you declared the function to return <type>void</type>, a
+      <command>RETURN</command> statement can be used to exit the function
+      early; but do not write an expression following
+      <command>RETURN</command>.
+     </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.  This restriction does not apply to functions
+      with output parameters and functions returning <type>void</type>,
+      however.  In those cases a <command>RETURN</command> statement is
+      automatically executed if the top-level block finishes.
+     </para>
+
+     <para>
+      Some examples:
+
+<programlisting>
+-- functions returning a scalar type
+RETURN 1 + 2;
+RETURN scalar_var;
+
+-- functions returning a composite type
+RETURN composite_type_var;
+RETURN (1, 2, 'three'::text);  -- must cast columns to correct types
+</programlisting>
+     </para>
+    </sect3>
+
+    <sect3 id="plpgsql-statements-returning-return-next">
+     <title><command>RETURN NEXT</command> and <command>RETURN QUERY</command></title>
+    <indexterm>
+     <primary>RETURN NEXT</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+    <indexterm>
+     <primary>RETURN QUERY</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+
+<synopsis>
+RETURN NEXT <replaceable>expression</replaceable>;
+RETURN QUERY <replaceable>query</replaceable>;
+RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
+</synopsis>
+
+     <para>
+      When a <application>PL/pgSQL</application> function is declared to return
+      <literal>SETOF <replaceable>sometype</replaceable></literal>, the procedure
+      to follow is slightly different.  In that case, the individual
+      items to return are specified by a sequence of <command>RETURN
+      NEXT</command> or <command>RETURN QUERY</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; with a composite result type, an entire
+      <quote>table</quote> of results will be returned.
+      <command>RETURN QUERY</command> appends the results of executing
+      a query to the function's result set. <command>RETURN
+      NEXT</command> and <command>RETURN QUERY</command> can be freely
+      intermixed in a single set-returning function, in which case
+      their results will be concatenated.
+     </para>
+
+     <para>
+      <command>RETURN NEXT</command> and <command>RETURN
+      QUERY</command> do not actually return from the function &mdash;
+      they simply append zero or more rows to the function's result
+      set.  Execution then continues with the next statement in the
+      <application>PL/pgSQL</application> function.  As successive
+      <command>RETURN NEXT</command> or <command>RETURN
+      QUERY</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 (or you can just
+      let control reach the end of the function).
+     </para>
+
+     <para>
+      <command>RETURN QUERY</command> has a variant
+      <command>RETURN QUERY EXECUTE</command>, which specifies the
+      query to be executed dynamically.  Parameter expressions can
+      be inserted into the computed query string via <literal>USING</literal>,
+      in just the same way as in the <command>EXECUTE</command> command.
+     </para>
+
+     <para>
+      If you declared the function with output parameters, write just
+      <command>RETURN NEXT</command> with no expression.  On each
+      execution, the current values of the output parameter
+      variable(s) will be saved for eventual return as a row of the
+      result.  Note that you must declare the function as returning
+      <literal>SETOF record</literal> when there are multiple output
+      parameters, or <literal>SETOF <replaceable>sometype</replaceable></literal>
+      when there is just one output parameter of type
+      <replaceable>sometype</replaceable>, in order to create a set-returning
+      function with output parameters.
+     </para>
+
+     <para>
+      Here is an example of a function using <command>RETURN
+      NEXT</command>:
+
+<programlisting>
+CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
+INSERT INTO foo VALUES (1, 2, 'three');
+INSERT INTO foo VALUES (4, 5, 'six');
+
+CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
+$BODY$
+DECLARE
+    r foo%rowtype;
+BEGIN
+    FOR r IN
+        SELECT * FROM foo WHERE fooid &gt; 0
+    LOOP
+        -- can do some processing here
+        RETURN NEXT r; -- return current row of SELECT
+    END LOOP;
+    RETURN;
+END;
+$BODY$
+LANGUAGE plpgsql;
+
+SELECT * FROM get_all_foo();
+</programlisting>
+     </para>
+
+     <para>
+      Here is an example of a function using <command>RETURN
+      QUERY</command>:
+
+<programlisting>
+CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
+$BODY$
+BEGIN
+    RETURN QUERY SELECT flightid
+                   FROM flight
+                  WHERE flightdate &gt;= $1
+                    AND flightdate &lt; ($1 + 1);
+
+    -- Since execution is not finished, we can check whether rows were returned
+    -- and raise exception if not.
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'No flight at %.', $1;
+    END IF;
+
+    RETURN;
+ END;
+$BODY$
+LANGUAGE plpgsql;
+
+-- Returns available flights or raises exception if there are no
+-- available flights.
+SELECT * FROM get_available_flightid(CURRENT_DATE);
+</programlisting>
+     </para>
+
+     <note>
+      <para>
+       The current implementation of <command>RETURN NEXT</command>
+       and <command>RETURN QUERY</command> stores the entire result set
+       before returning from the function, as discussed above.  That
+       means that if a <application>PL/pgSQL</application> function produces a
+       very large result set, performance might 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</application> might
+       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
+       <xref linkend="guc-work-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-statements-returning-procedure">
+    <title>Returning from a Procedure</title>
+
+    <para>
+     A procedure does not have a return value.  A procedure can therefore end
+     without a <command>RETURN</command> statement.  If you wish to use
+     a <command>RETURN</command> statement to exit the code early, write
+     just <command>RETURN</command> with no expression.
+    </para>
+
+    <para>
+     If the procedure has output parameters, the final values of the output
+     parameter variables will be returned to the caller.
+    </para>
+   </sect2>
+
+   <sect2 id="plpgsql-statements-calling-procedure">
+    <title>Calling a Procedure</title>
+
+    <para>
+     A <application>PL/pgSQL</application> function, procedure,
+     or <command>DO</command> block can call a procedure
+     using <command>CALL</command>.  Output parameters are handled
+     differently from the way that <command>CALL</command> works in plain
+     SQL.  Each <literal>OUT</literal> or <literal>INOUT</literal>
+     parameter of the procedure must
+     correspond to a variable in the <command>CALL</command> statement, and
+     whatever the procedure returns is assigned back to that variable after
+     it returns.  For example:
+<programlisting>
+CREATE PROCEDURE triple(INOUT x int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    x := x * 3;
+END;
+$$;
+
+DO $$
+DECLARE myvar int := 5;
+BEGIN
+  CALL triple(myvar);
+  RAISE NOTICE 'myvar = %', myvar;  -- prints 15
+END;
+$$;
+</programlisting>
+     The variable corresponding to an output parameter can be a simple
+     variable or a field of a composite-type variable.  Currently,
+     it cannot be an element of an array.
+    </para>
+   </sect2>
+
+   <sect2 id="plpgsql-conditionals">
+    <title>Conditionals</title>
+
+    <para>
+     <command>IF</command> and <command>CASE</command> statements let you execute
+     alternative commands based on certain conditions.
+     <application>PL/pgSQL</application> has three forms of <command>IF</command>:
+    <itemizedlist>
+     <listitem>
+      <para><literal>IF ... THEN ... END IF</literal></para>
+     </listitem>
+     <listitem>
+      <para><literal>IF ... THEN ... ELSE ... END IF</literal></para>
+     </listitem>
+     <listitem>
+      <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF</literal></para>
+     </listitem>
+    </itemizedlist>
+
+    and two forms of <command>CASE</command>:
+    <itemizedlist>
+     <listitem>
+      <para><literal>CASE ... WHEN ... THEN ... ELSE ... END CASE</literal></para>
+     </listitem>
+     <listitem>
+      <para><literal>CASE WHEN ... THEN ... ELSE ... END CASE</literal></para>
+     </listitem>
+    </itemizedlist>
+    </para>
+
+    <sect3 id="plpgsql-conditionals-if-then">
+     <title><literal>IF-THEN</literal></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 id="plpgsql-conditionals-if-then-else">
+      <title><literal>IF-THEN-ELSE</literal></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 is not true.  (Note this includes the case where the
+        condition evaluates to NULL.)
+       </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 &gt; 0 THEN
+    INSERT INTO users_count (count) VALUES (v_count);
+    RETURN 't';
+ELSE
+    RETURN 'f';
+END IF;
+</programlisting>
+     </para>
+    </sect3>
+
+     <sect3 id="plpgsql-conditionals-if-then-elsif">
+      <title><literal>IF-THEN-ELSIF</literal></title>
+
+<synopsis>
+IF <replaceable>boolean-expression</replaceable> THEN
+    <replaceable>statements</replaceable>
+<optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
+    <replaceable>statements</replaceable>
+<optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
+    <replaceable>statements</replaceable>
+    ...
+</optional>
+</optional>
+<optional> ELSE
+    <replaceable>statements</replaceable> </optional>
+END IF;
+</synopsis>
+
+       <para>
+        Sometimes there are more than just two alternatives.
+        <literal>IF-THEN-ELSIF</literal> provides a convenient
+        method of checking several alternatives in turn.
+        The <literal>IF</literal> conditions are tested successively
+        until the first one that is true is found.  Then the
+        associated statement(s) are executed, after which control
+        passes to the next statement after <literal>END IF</literal>.
+        (Any subsequent <literal>IF</literal> conditions are <emphasis>not</emphasis>
+        tested.)  If none of the <literal>IF</literal> conditions is true,
+        then the <literal>ELSE</literal> block (if any) is executed.
+       </para>
+
+       <para>
+        Here is an example:
+
+<programlisting>
+IF number = 0 THEN
+    result := 'zero';
+ELSIF number &gt; 0 THEN
+    result := 'positive';
+ELSIF number &lt; 0 THEN
+    result := 'negative';
+ELSE
+    -- hmm, the only other possibility is that number is null
+    result := 'NULL';
+END IF;
+</programlisting>
+       </para>
+
+       <para>
+        The key word <literal>ELSIF</literal> can also be spelled
+        <literal>ELSEIF</literal>.
+       </para>
+
+       <para>
+        An alternative way of accomplishing the same task is to nest
+        <literal>IF-THEN-ELSE</literal> statements, as in the
+        following example:
+
+<programlisting>
+IF demo_row.sex = 'm' THEN
+    pretty_sex := 'man';
+ELSE
+    IF demo_row.sex = 'f' THEN
+        pretty_sex := 'woman';
+    END IF;
+END IF;
+</programlisting>
+       </para>
+
+       <para>
+        However, this method requires writing a matching <literal>END IF</literal>
+        for each <literal>IF</literal>, so it is much more cumbersome than
+        using <literal>ELSIF</literal> when there are many alternatives.
+       </para>
+     </sect3>
+
+     <sect3 id="plpgsql-conditionals-simple-case">
+      <title>Simple <literal>CASE</literal></title>
+
+<synopsis>
+CASE <replaceable>search-expression</replaceable>
+    WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
+      <replaceable>statements</replaceable>
+  <optional> WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
+      <replaceable>statements</replaceable>
+    ... </optional>
+  <optional> ELSE
+      <replaceable>statements</replaceable> </optional>
+END CASE;
+</synopsis>
+
+      <para>
+       The simple form of <command>CASE</command> provides conditional execution
+       based on equality of operands.  The <replaceable>search-expression</replaceable>
+       is evaluated (once) and successively compared to each
+       <replaceable>expression</replaceable> in the <literal>WHEN</literal> clauses.
+       If a match is found, then the corresponding
+       <replaceable>statements</replaceable> are executed, and then control
+       passes to the next statement after <literal>END CASE</literal>.  (Subsequent
+       <literal>WHEN</literal> expressions are not evaluated.)  If no match is
+       found, the <literal>ELSE</literal> <replaceable>statements</replaceable> are
+       executed; but if <literal>ELSE</literal> is not present, then a
+       <literal>CASE_NOT_FOUND</literal> exception is raised.
+      </para>
+
+      <para>
+       Here is a simple example:
+
+<programlisting>
+CASE x
+    WHEN 1, 2 THEN
+        msg := 'one or two';
+    ELSE
+        msg := 'other value than one or two';
+END CASE;
+</programlisting>
+      </para>
+     </sect3>
+
+     <sect3 id="plpgsql-conditionals-searched-case">
+      <title>Searched <literal>CASE</literal></title>
+
+<synopsis>
+CASE
+    WHEN <replaceable>boolean-expression</replaceable> THEN
+      <replaceable>statements</replaceable>
+  <optional> WHEN <replaceable>boolean-expression</replaceable> THEN
+      <replaceable>statements</replaceable>
+    ... </optional>
+  <optional> ELSE
+      <replaceable>statements</replaceable> </optional>
+END CASE;
+</synopsis>
+
+      <para>
+       The searched form of <command>CASE</command> provides conditional execution
+       based on truth of Boolean expressions.  Each <literal>WHEN</literal> clause's
+       <replaceable>boolean-expression</replaceable> is evaluated in turn,
+       until one is found that yields <literal>true</literal>.  Then the
+       corresponding <replaceable>statements</replaceable> are executed, and
+       then control passes to the next statement after <literal>END CASE</literal>.
+       (Subsequent <literal>WHEN</literal> expressions are not evaluated.)
+       If no true result is found, the <literal>ELSE</literal>
+       <replaceable>statements</replaceable> are executed;
+       but if <literal>ELSE</literal> is not present, then a
+       <literal>CASE_NOT_FOUND</literal> exception is raised.
+      </para>
+
+      <para>
+       Here is an example:
+
+<programlisting>
+CASE
+    WHEN x BETWEEN 0 AND 10 THEN
+        msg := 'value is between zero and ten';
+    WHEN x BETWEEN 11 AND 20 THEN
+        msg := 'value is between eleven and twenty';
+END CASE;
+</programlisting>
+      </para>
+
+      <para>
+       This form of <command>CASE</command> is entirely equivalent to
+       <literal>IF-THEN-ELSIF</literal>, except for the rule that reaching
+       an omitted <literal>ELSE</literal> clause results in an error rather
+       than doing nothing.
+      </para>
+
+     </sect3>
+   </sect2>
+
+   <sect2 id="plpgsql-control-structures-loops">
+    <title>Simple Loops</title>
+
+    <indexterm zone="plpgsql-control-structures-loops">
+     <primary>loop</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+
+    <para>
+     With the <literal>LOOP</literal>, <literal>EXIT</literal>,
+     <literal>CONTINUE</literal>, <literal>WHILE</literal>, <literal>FOR</literal>,
+     and <literal>FOREACH</literal> statements, you can arrange for your
+     <application>PL/pgSQL</application> function to repeat a series of commands.
+    </para>
+
+    <sect3 id="plpgsql-control-structures-loops-loop">
+     <title><literal>LOOP</literal></title>
+
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+LOOP
+    <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+
+     <para>
+      <literal>LOOP</literal> defines an unconditional loop that is repeated
+      indefinitely until terminated by an <literal>EXIT</literal> or
+      <command>RETURN</command> statement.  The optional
+      <replaceable>label</replaceable> can be used by <literal>EXIT</literal>
+      and <literal>CONTINUE</literal> statements within nested loops to
+      specify which loop those statements refer to.
+     </para>
+    </sect3>
+
+     <sect3 id="plpgsql-control-structures-loops-exit">
+      <title><literal>EXIT</literal></title>
+
+     <indexterm>
+      <primary>EXIT</primary>
+      <secondary>in PL/pgSQL</secondary>
+     </indexterm>
+
+<synopsis>
+EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
+</synopsis>
+
+       <para>
+        If no <replaceable>label</replaceable> is given, the innermost
+        loop is terminated and the statement following <literal>END
+        LOOP</literal> is executed next.  If <replaceable>label</replaceable>
+        is given, it must be the label of the current or some outer
+        level of nested loop or block. Then the named loop or block is
+        terminated and control continues with the statement after the
+        loop's/block's corresponding <literal>END</literal>.
+       </para>
+
+       <para>
+        If <literal>WHEN</literal> is specified, the loop exit occurs only if
+        <replaceable>boolean-expression</replaceable> is true. Otherwise, control passes
+        to the statement after <literal>EXIT</literal>.
+       </para>
+
+       <para>
+        <literal>EXIT</literal> can be used with all types of loops; it is
+        not limited to use with unconditional loops.
+       </para>
+
+       <para>
+        When used with a
+        <literal>BEGIN</literal> block, <literal>EXIT</literal> passes
+        control to the next statement after the end of the block.
+        Note that a label must be used for this purpose; an unlabeled
+        <literal>EXIT</literal> is never considered to match a
+        <literal>BEGIN</literal> block.  (This is a change from
+        pre-8.4 releases of <productname>PostgreSQL</productname>, which
+        would allow an unlabeled <literal>EXIT</literal> to match
+        a <literal>BEGIN</literal> block.)
+       </para>
+
+       <para>
+        Examples:
+<programlisting>
+LOOP
+    -- some computations
+    IF count &gt; 0 THEN
+        EXIT;  -- exit loop
+    END IF;
+END LOOP;
+
+LOOP
+    -- some computations
+    EXIT WHEN count &gt; 0;  -- same result as previous example
+END LOOP;
+
+&lt;&lt;ablock&gt;&gt;
+BEGIN
+    -- some computations
+    IF stocks &gt; 100000 THEN
+        EXIT ablock;  -- causes exit from the BEGIN block
+    END IF;
+    -- computations here will be skipped when stocks &gt; 100000
+END;
+</programlisting>
+       </para>
+     </sect3>
+
+     <sect3 id="plpgsql-control-structures-loops-continue">
+      <title><literal>CONTINUE</literal></title>
+
+     <indexterm>
+      <primary>CONTINUE</primary>
+      <secondary>in PL/pgSQL</secondary>
+     </indexterm>
+
+<synopsis>
+CONTINUE <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
+</synopsis>
+
+       <para>
+        If no <replaceable>label</replaceable> is given, the next iteration of
+        the innermost loop is begun. That is, all statements remaining
+        in the loop body are skipped, and control returns
+        to the loop control expression (if any) to determine whether
+        another loop iteration is needed.
+        If <replaceable>label</replaceable> is present, it
+        specifies the label of the loop whose execution will be
+        continued.
+       </para>
+
+       <para>
+        If <literal>WHEN</literal> is specified, the next iteration of the
+        loop is begun only if <replaceable>boolean-expression</replaceable> is
+        true. Otherwise, control passes to the statement after
+        <literal>CONTINUE</literal>.
+       </para>
+
+       <para>
+        <literal>CONTINUE</literal> can be used with all types of loops; it
+        is not limited to use with unconditional loops.
+       </para>
+
+       <para>
+        Examples:
+<programlisting>
+LOOP
+    -- some computations
+    EXIT WHEN count &gt; 100;
+    CONTINUE WHEN count &lt; 50;
+    -- some computations for count IN [50 .. 100]
+END LOOP;
+</programlisting>
+       </para>
+     </sect3>
+
+
+     <sect3 id="plpgsql-control-structures-loops-while">
+      <title><literal>WHILE</literal></title>
+
+     <indexterm>
+      <primary>WHILE</primary>
+      <secondary>in PL/pgSQL</secondary>
+     </indexterm>
+
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+WHILE <replaceable>boolean-expression</replaceable> LOOP
+    <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+
+       <para>
+        The <literal>WHILE</literal> statement repeats a
+        sequence of statements so long as the
+        <replaceable>boolean-expression</replaceable>
+        evaluates to true.  The expression is checked just before
+        each entry to the loop body.
+       </para>
+
+       <para>
+        For example:
+<programlisting>
+WHILE amount_owed &gt; 0 AND gift_certificate_balance &gt; 0 LOOP
+    -- some computations here
+END LOOP;
+
+WHILE NOT done LOOP
+    -- some computations here
+END LOOP;
+</programlisting>
+       </para>
+     </sect3>
+
+     <sect3 id="plpgsql-integer-for">
+      <title><literal>FOR</literal> (Integer Variant)</title>
+
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional> LOOP
+    <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+
+       <para>
+        This form of <literal>FOR</literal> creates a loop that iterates over a range
+        of integer values. The variable
+        <replaceable>name</replaceable> is automatically defined as type
+        <type>integer</type> and exists only inside the loop (any existing
+        definition of the variable name is ignored within the loop).
+        The two expressions giving
+        the lower and upper bound of the range are evaluated once when entering
+        the loop. If the <literal>BY</literal> clause isn't specified the iteration
+        step is 1, otherwise it's the value specified in the <literal>BY</literal>
+        clause, which again is evaluated once on loop entry.
+        If <literal>REVERSE</literal> is specified then the step value is
+        subtracted, rather than added, after each iteration.
+       </para>
+
+       <para>
+        Some examples of integer <literal>FOR</literal> loops:
+<programlisting>
+FOR i IN 1..10 LOOP
+    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
+END LOOP;
+
+FOR i IN REVERSE 10..1 LOOP
+    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
+END LOOP;
+
+FOR i IN REVERSE 10..1 BY 2 LOOP
+    -- i will take on the values 10,8,6,4,2 within the loop
+END LOOP;
+</programlisting>
+       </para>
+
+       <para>
+        If the lower bound is greater than the upper bound (or less than,
+        in the <literal>REVERSE</literal> case), the loop body is not
+        executed at all.  No error is raised.
+       </para>
+
+       <para>
+        If a <replaceable>label</replaceable> is attached to the
+        <literal>FOR</literal> loop then the integer loop variable can be
+        referenced with a qualified name, using that
+        <replaceable>label</replaceable>.
+       </para>
+     </sect3>
+   </sect2>
+
+   <sect2 id="plpgsql-records-iterating">
+    <title>Looping through Query Results</title>
+
+    <para>
+     Using a different type of <literal>FOR</literal> loop, you can iterate through
+     the results of a query and manipulate that data
+     accordingly. The syntax is:
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+FOR <replaceable>target</replaceable> IN <replaceable>query</replaceable> LOOP
+    <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+     The <replaceable>target</replaceable> is a record variable, row variable,
+     or comma-separated list of scalar variables.
+     The <replaceable>target</replaceable> is successively assigned each row
+     resulting from the <replaceable>query</replaceable> and the loop body is
+     executed for each row. Here is an example:
+<programlisting>
+CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
+DECLARE
+    mviews RECORD;
+BEGIN
+    RAISE NOTICE 'Refreshing all materialized views...';
+
+    FOR mviews IN
+       SELECT n.nspname AS mv_schema,
+              c.relname AS mv_name,
+              pg_catalog.pg_get_userbyid(c.relowner) AS owner
+         FROM pg_catalog.pg_class c
+    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
+        WHERE c.relkind = 'm'
+     ORDER BY 1
+    LOOP
+
+        -- Now "mviews" has one record with information about the materialized view
+
+        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
+                     quote_ident(mviews.mv_schema),
+                     quote_ident(mviews.mv_name),
+                     quote_ident(mviews.owner);
+        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
+    END LOOP;
+
+    RAISE NOTICE 'Done refreshing materialized views.';
+    RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+     If the loop is terminated by an <literal>EXIT</literal> statement, the last
+     assigned row value is still accessible after the loop.
+    </para>
+
+    <para>
+     The <replaceable>query</replaceable> used in this type of <literal>FOR</literal>
+     statement can be any SQL command that returns rows to the caller:
+     <command>SELECT</command> is the most common case,
+     but you can also use <command>INSERT</command>, <command>UPDATE</command>, or
+     <command>DELETE</command> with a <literal>RETURNING</literal> clause.  Some utility
+     commands such as <command>EXPLAIN</command> will work too.
+    </para>
+
+    <para>
+     <application>PL/pgSQL</application> variables are replaced by query parameters,
+     and the query plan is cached for possible re-use, as discussed in
+     detail in <xref linkend="plpgsql-var-subst"/> and
+     <xref linkend="plpgsql-plan-caching"/>.
+    </para>
+
+    <para>
+     The <literal>FOR-IN-EXECUTE</literal> statement is another way to iterate over
+     rows:
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional> LOOP
+    <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+     This is like the previous form, except that the source query
+     is specified as a string expression, which is evaluated and replanned
+     on each entry to the <literal>FOR</literal> loop.  This allows the programmer to
+     choose the speed of a preplanned query or the flexibility of a dynamic
+     query, just as with a plain <command>EXECUTE</command> statement.
+     As with <command>EXECUTE</command>, parameter values can be inserted
+     into the dynamic command via <literal>USING</literal>.
+    </para>
+
+    <para>
+     Another way to specify the query whose results should be iterated
+     through is to declare it as a cursor.  This is described in
+     <xref linkend="plpgsql-cursor-for-loop"/>.
+    </para>
+   </sect2>
+
+   <sect2 id="plpgsql-foreach-array">
+    <title>Looping through Arrays</title>
+
+    <para>
+     The <literal>FOREACH</literal> loop is much like a <literal>FOR</literal> loop,
+     but instead of iterating through the rows returned by an SQL query,
+     it iterates through the elements of an array value.
+     (In general, <literal>FOREACH</literal> is meant for looping through
+     components of a composite-valued expression; variants for looping
+     through composites besides arrays may be added in future.)
+     The <literal>FOREACH</literal> statement to loop over an array is:
+
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+FOREACH <replaceable>target</replaceable> <optional> SLICE <replaceable>number</replaceable> </optional> IN ARRAY <replaceable>expression</replaceable> LOOP
+    <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+    </para>
+
+    <para>
+     Without <literal>SLICE</literal>, or if <literal>SLICE 0</literal> is specified,
+     the loop iterates through individual elements of the array produced
+     by evaluating the <replaceable>expression</replaceable>.
+     The <replaceable>target</replaceable> variable is assigned each
+     element value in sequence, and the loop body is executed for each element.
+     Here is an example of looping through the elements of an integer
+     array:
+
+<programlisting>
+CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
+DECLARE
+  s int8 := 0;
+  x int;
+BEGIN
+  FOREACH x IN ARRAY $1
+  LOOP
+    s := s + x;
+  END LOOP;
+  RETURN s;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+     The elements are visited in storage order, regardless of the number of
+     array dimensions.  Although the <replaceable>target</replaceable> is
+     usually just a single variable, it can be a list of variables when
+     looping through an array of composite values (records).  In that case,
+     for each array element, the variables are assigned from successive
+     columns of the composite value.
+    </para>
+
+    <para>
+     With a positive <literal>SLICE</literal> value, <literal>FOREACH</literal>
+     iterates through slices of the array rather than single elements.
+     The <literal>SLICE</literal> value must be an integer constant not larger
+     than the number of dimensions of the array.  The
+     <replaceable>target</replaceable> variable must be an array,
+     and it receives successive slices of the array value, where each slice
+     is of the number of dimensions specified by <literal>SLICE</literal>.
+     Here is an example of iterating through one-dimensional slices:
+
+<programlisting>
+CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
+DECLARE
+  x int[];
+BEGIN
+  FOREACH x SLICE 1 IN ARRAY $1
+  LOOP
+    RAISE NOTICE 'row = %', x;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
+
+NOTICE:  row = {1,2,3}
+NOTICE:  row = {4,5,6}
+NOTICE:  row = {7,8,9}
+NOTICE:  row = {10,11,12}
+</programlisting>
+    </para>
+   </sect2>
+
+   <sect2 id="plpgsql-error-trapping">
+    <title>Trapping Errors</title>
+
+    <indexterm>
+     <primary>exceptions</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+
+    <para>
+     By default, any error occurring in a <application>PL/pgSQL</application>
+     function aborts execution of the function and the
+     surrounding transaction.  You can trap errors and recover
+     from them by using a <command>BEGIN</command> block with an
+     <literal>EXCEPTION</literal> clause.  The syntax is an extension of the
+     normal syntax for a <command>BEGIN</command> block:
+
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+<optional> DECLARE
+    <replaceable>declarations</replaceable> </optional>
+BEGIN
+    <replaceable>statements</replaceable>
+EXCEPTION
+    WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
+        <replaceable>handler_statements</replaceable>
+    <optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
+          <replaceable>handler_statements</replaceable>
+      ... </optional>
+END;
+</synopsis>
+    </para>
+
+    <para>
+     If no error occurs, this form of block simply executes all the
+     <replaceable>statements</replaceable>, and then control passes
+     to the next statement after <literal>END</literal>.  But if an error
+     occurs within the <replaceable>statements</replaceable>, further
+     processing of the <replaceable>statements</replaceable> is
+     abandoned, and control passes to the <literal>EXCEPTION</literal> list.
+     The list is searched for the first <replaceable>condition</replaceable>
+     matching the error that occurred.  If a match is found, the
+     corresponding <replaceable>handler_statements</replaceable> are
+     executed, and then control passes to the next statement after
+     <literal>END</literal>.  If no match is found, the error propagates out
+     as though the <literal>EXCEPTION</literal> clause were not there at all:
+     the error can be caught by an enclosing block with
+     <literal>EXCEPTION</literal>, or if there is none it aborts processing
+     of the function.
+    </para>
+
+    <para>
+     The <replaceable>condition</replaceable> names can be any of
+     those shown in <xref linkend="errcodes-appendix"/>.  A category
+     name matches any error within its category.  The special
+     condition name <literal>OTHERS</literal> matches every error type except
+     <literal>QUERY_CANCELED</literal> and <literal>ASSERT_FAILURE</literal>.
+     (It is possible, but often unwise, to trap those two error types
+     by name.)  Condition names are
+     not case-sensitive.  Also, an error condition can be specified
+     by <literal>SQLSTATE</literal> code; for example these are equivalent:
+<programlisting>
+WHEN division_by_zero THEN ...
+WHEN SQLSTATE '22012' THEN ...
+</programlisting>
+    </para>
+
+    <para>
+     If a new error occurs within the selected
+     <replaceable>handler_statements</replaceable>, it cannot be caught
+     by this <literal>EXCEPTION</literal> clause, but is propagated out.
+     A surrounding <literal>EXCEPTION</literal> clause could catch it.
+    </para>
+
+    <para>
+     When an error is caught by an <literal>EXCEPTION</literal> clause,
+     the local variables of the <application>PL/pgSQL</application> function
+     remain as they were when the error occurred, but all changes
+     to persistent database state within the block are rolled back.
+     As an example, consider this fragment:
+
+<programlisting>
+INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
+BEGIN
+    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
+    x := x + 1;
+    y := x / 0;
+EXCEPTION
+    WHEN division_by_zero THEN
+        RAISE NOTICE 'caught division_by_zero';
+        RETURN x;
+END;
+</programlisting>
+
+     When control reaches the assignment to <literal>y</literal>, it will
+     fail with a <literal>division_by_zero</literal> error.  This will be caught by
+     the <literal>EXCEPTION</literal> clause.  The value returned in the
+     <command>RETURN</command> statement will be the incremented value of
+     <literal>x</literal>, but the effects of the <command>UPDATE</command> command will
+     have been rolled back.  The <command>INSERT</command> command preceding the
+     block is not rolled back, however, so the end result is that the database
+     contains <literal>Tom Jones</literal> not <literal>Joe Jones</literal>.
+    </para>
+
+    <tip>
+     <para>
+      A block containing an <literal>EXCEPTION</literal> clause is significantly
+      more expensive to enter and exit than a block without one.  Therefore,
+      don't use <literal>EXCEPTION</literal> without need.
+     </para>
+    </tip>
+
+    <example id="plpgsql-upsert-example">
+    <title>Exceptions with <command>UPDATE</command>/<command>INSERT</command></title>
+    <para>
+
+    This example uses exception handling to perform either
+    <command>UPDATE</command> or <command>INSERT</command>, as appropriate.  It is
+    recommended that applications use <command>INSERT</command> with
+    <literal>ON CONFLICT DO UPDATE</literal> rather than actually using
+    this pattern.  This example serves primarily to illustrate use of
+    <application>PL/pgSQL</application> control flow structures:
+
+<programlisting>
+CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
+
+CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
+$$
+BEGIN
+    LOOP
+        -- first try to update the key
+        UPDATE db SET b = data WHERE a = key;
+        IF found THEN
+            RETURN;
+        END IF;
+        -- not there, so try to insert the key
+        -- if someone else inserts the same key concurrently,
+        -- we could get a unique-key failure
+        BEGIN
+            INSERT INTO db(a,b) VALUES (key, data);
+            RETURN;
+        EXCEPTION WHEN unique_violation THEN
+            -- Do nothing, and loop to try the UPDATE again.
+        END;
+    END LOOP;
+END;
+$$
+LANGUAGE plpgsql;
+
+SELECT merge_db(1, 'david');
+SELECT merge_db(1, 'dennis');
+</programlisting>
+
+     This coding assumes the <literal>unique_violation</literal> error is caused by
+     the <command>INSERT</command>, and not by, say, an <command>INSERT</command> in a
+     trigger function on the table.  It might also misbehave if there is
+     more than one unique index on the table, since it will retry the
+     operation regardless of which index caused the error.
+     More safety could be had by using the
+     features discussed next to check that the trapped error was the one
+     expected.
+    </para>
+    </example>
+
+   <sect3 id="plpgsql-exception-diagnostics">
+    <title>Obtaining Information about an Error</title>
+
+    <para>
+     Exception handlers frequently need to identify the specific error that
+     occurred.  There are two ways to get information about the current
+     exception in <application>PL/pgSQL</application>: special variables and the
+     <command>GET STACKED DIAGNOSTICS</command> command.
+    </para>
+
+    <para>
+     Within an exception handler, the special variable
+     <varname>SQLSTATE</varname> contains the error code that corresponds to
+     the exception that was raised (refer to <xref linkend="errcodes-table"/>
+     for a list of possible error codes). The special variable
+     <varname>SQLERRM</varname> contains the error message associated with the
+     exception. These variables are undefined outside exception handlers.
+    </para>
+
+    <para>
+     Within an exception handler, one may also retrieve
+     information about the current exception by using the
+     <command>GET STACKED DIAGNOSTICS</command> command, which has the form:
+
+<synopsis>
+GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replaceable>item</replaceable> <optional> , ... </optional>;
+</synopsis>
+
+     Each <replaceable>item</replaceable> is a key word identifying a status
+     value to be assigned to the specified <replaceable>variable</replaceable>
+     (which should be of the right data type to receive it).  The currently
+     available status items are shown
+     in <xref linkend="plpgsql-exception-diagnostics-values"/>.
+    </para>
+
+     <table id="plpgsql-exception-diagnostics-values">
+      <title>Error Diagnostics Items</title>
+      <tgroup cols="3">
+       <colspec colname="col1" colwidth="2*"/>
+       <colspec colname="col2" colwidth="1*"/>
+       <colspec colname="col3" colwidth="2*"/>
+       <thead>
+        <row>
+         <entry>Name</entry>
+         <entry>Type</entry>
+         <entry>Description</entry>
+        </row>
+       </thead>
+       <tbody>
+        <row>
+         <entry><literal>RETURNED_SQLSTATE</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the SQLSTATE error code of the exception</entry>
+        </row>
+        <row>
+         <entry><literal>COLUMN_NAME</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the name of the column related to exception</entry>
+        </row>
+        <row>
+         <entry><literal>CONSTRAINT_NAME</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the name of the constraint related to exception</entry>
+        </row>
+        <row>
+         <entry><literal>PG_DATATYPE_NAME</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the name of the data type related to exception</entry>
+        </row>
+        <row>
+         <entry><literal>MESSAGE_TEXT</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the text of the exception's primary message</entry>
+        </row>
+        <row>
+         <entry><literal>TABLE_NAME</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the name of the table related to exception</entry>
+        </row>
+        <row>
+         <entry><literal>SCHEMA_NAME</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the name of the schema related to exception</entry>
+        </row>
+        <row>
+         <entry><literal>PG_EXCEPTION_DETAIL</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the text of the exception's detail message, if any</entry>
+        </row>
+        <row>
+         <entry><literal>PG_EXCEPTION_HINT</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>the text of the exception's hint message, if any</entry>
+        </row>
+        <row>
+         <entry><literal>PG_EXCEPTION_CONTEXT</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>line(s) of text describing the call stack at the time of the
+          exception (see <xref linkend="plpgsql-call-stack"/>)</entry>
+        </row>
+       </tbody>
+      </tgroup>
+     </table>
+
+    <para>
+     If the exception did not set a value for an item, an empty string
+     will be returned.
+    </para>
+
+    <para>
+     Here is an example:
+<programlisting>
+DECLARE
+  text_var1 text;
+  text_var2 text;
+  text_var3 text;
+BEGIN
+  -- some processing which might cause an exception
+  ...
+EXCEPTION WHEN OTHERS THEN
+  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
+                          text_var2 = PG_EXCEPTION_DETAIL,
+                          text_var3 = PG_EXCEPTION_HINT;
+END;
+</programlisting>
+    </para>
+   </sect3>
+  </sect2>
+
+  <sect2 id="plpgsql-call-stack">
+   <title>Obtaining Execution Location Information</title>
+
+   <para>
+    The <command>GET DIAGNOSTICS</command> command, previously described
+    in <xref linkend="plpgsql-statements-diagnostics"/>, retrieves information
+    about current execution state (whereas the <command>GET STACKED
+    DIAGNOSTICS</command> command discussed above reports information about
+    the execution state as of a previous error).  Its <literal>PG_CONTEXT</literal>
+    status item is useful for identifying the current execution
+    location.  <literal>PG_CONTEXT</literal> returns a text string with line(s)
+    of text describing the call stack.  The first line refers to the current
+    function and currently executing <command>GET DIAGNOSTICS</command>
+    command.  The second and any subsequent lines refer to calling functions
+    further up the call stack.  For example:
+
+<programlisting>
+CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
+BEGIN
+  RETURN inner_func();
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
+DECLARE
+  stack text;
+BEGIN
+  GET DIAGNOSTICS stack = PG_CONTEXT;
+  RAISE NOTICE E'--- Call Stack ---\n%', stack;
+  RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT outer_func();
+
+NOTICE:  --- Call Stack ---
+PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
+PL/pgSQL function outer_func() line 3 at RETURN
+CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
+ outer_func
+ ------------
+           1
+(1 row)
+</programlisting>
+
+   </para>
+
+   <para>
+    <literal>GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT</literal>
+    returns the same sort of stack trace, but describing the location
+    at which an error was detected, rather than the current location.
+   </para>
+  </sect2>
+  </sect1>
+
+  <sect1 id="plpgsql-cursors">
+   <title>Cursors</title>
+
+   <indexterm zone="plpgsql-cursors">
+    <primary>cursor</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <para>
+    Rather than executing a whole query at once, it is possible to set
+    up a <firstterm>cursor</firstterm> that encapsulates the query, and then read
+    the query result a few rows at a time. One reason for doing this is
+    to avoid memory overrun when the result contains a large number of
+    rows. (However, <application>PL/pgSQL</application> users do not normally need
+    to worry about that, since <literal>FOR</literal> loops automatically use a cursor
+    internally to avoid memory problems.) A more interesting usage is to
+    return a reference to a cursor that a function has created, allowing the
+    caller to read the rows. This provides an efficient way to return
+    large row sets from functions.
+   </para>
+
+   <sect2 id="plpgsql-cursor-declarations">
+    <title>Declaring Cursor Variables</title>
+
+    <para>
+     All access to cursors in <application>PL/pgSQL</application> goes through
+     cursor variables, which are always of the special data type
+     <type>refcursor</type>.  One way to create a cursor variable
+     is just to declare it as a variable of type <type>refcursor</type>.
+     Another way is to use the cursor declaration syntax,
+     which in general is:
+<synopsis>
+<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
+</synopsis>
+     (<literal>FOR</literal> can be replaced by <literal>IS</literal> for
+     <productname>Oracle</productname> compatibility.)
+     If <literal>SCROLL</literal> is specified, the cursor will be capable of
+     scrolling backward; if <literal>NO SCROLL</literal> is specified, backward
+     fetches will be rejected; if neither specification appears, it is
+     query-dependent whether backward fetches will be allowed.
+     <replaceable>arguments</replaceable>, if specified, is a
+     comma-separated list of pairs <literal><replaceable>name</replaceable>
+     <replaceable>datatype</replaceable></literal> that define names to be
+     replaced by parameter values in the given query.  The actual
+     values to substitute for these names will be specified later,
+     when the cursor is opened.
+    </para>
+    <para>
+     Some examples:
+<programlisting>
+DECLARE
+    curs1 refcursor;
+    curs2 CURSOR FOR SELECT * FROM tenk1;
+    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
+</programlisting>
+     All three of these variables have the data type <type>refcursor</type>,
+     but the first can be used with any query, while the second has
+     a fully specified query already <firstterm>bound</firstterm> to it, and the last
+     has a parameterized query bound to it.  (<literal>key</literal> will be
+     replaced by an integer parameter value when the cursor is opened.)
+     The variable <literal>curs1</literal>
+     is said to be <firstterm>unbound</firstterm> since it is not bound to
+     any particular query.
+    </para>
+
+    <para>
+     The <literal>SCROLL</literal> option cannot be used when the cursor's
+     query uses <literal>FOR UPDATE/SHARE</literal>.  Also, it is
+     best to use <literal>NO SCROLL</literal> with a query that involves
+     volatile functions.  The implementation of <literal>SCROLL</literal>
+     assumes that re-reading the query's output will give consistent
+     results, which a volatile function might not do.
+    </para>
+   </sect2>
+
+   <sect2 id="plpgsql-cursor-opening">
+    <title>Opening Cursors</title>
+
+    <para>
+     Before a cursor can be used to retrieve rows, it must be
+     <firstterm>opened</firstterm>. (This is the equivalent action to the SQL
+     command <link linkend="sql-declare"><command>DECLARE
+     CURSOR</command></link>.)
+     <application>PL/pgSQL</application> has
+     three forms of the <command>OPEN</command> statement, two of which use unbound
+     cursor variables while the third uses a bound cursor variable.
+    </para>
+
+    <note>
+     <para>
+      Bound cursor variables can also be used without explicitly opening the cursor,
+      via the <command>FOR</command> statement described in
+      <xref linkend="plpgsql-cursor-for-loop"/>.
+      A <command>FOR</command> loop will open the cursor and then
+      close it again when the loop completes.
+     </para>
+    </note>
+
+    <indexterm>
+     <primary>portal</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+
+    <para>
+     Opening a cursor involves creating a server-internal data structure
+     called a <firstterm>portal</firstterm>, which holds the execution
+     state for the cursor's query.  A portal has a name, which must be
+     unique within the session for the duration of the portal's existence.
+     By default, <application>PL/pgSQL</application> will assign a unique
+     name to each portal it creates.  However, if you assign a non-null
+     string value to a cursor variable, that string will be used as its
+     portal name.  This feature can be used as described in
+     <xref linkend="plpgsql-cursor-returning"/>.
+    </para>
+
+    <sect3 id="plpgsql-cursor-opening-open-for-query">
+     <title><command>OPEN FOR</command> <replaceable>query</replaceable></title>
+
+<synopsis>
+OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>;
+</synopsis>
+
+       <para>
+        The cursor variable is opened and given the specified query to
+        execute.  The cursor cannot be open already, and it must have been
+        declared as an unbound cursor variable (that is, as a simple
+        <type>refcursor</type> variable).  The query must be a
+        <command>SELECT</command>, or something else that returns rows
+        (such as <command>EXPLAIN</command>).  The query
+        is treated in the same way as other SQL commands in
+        <application>PL/pgSQL</application>: <application>PL/pgSQL</application>
+        variable names are substituted, and the query plan is cached for
+        possible reuse.  When a <application>PL/pgSQL</application>
+        variable is substituted into the cursor query, the value that is
+        substituted is the one it has at the time of the <command>OPEN</command>;
+        subsequent changes to the variable will not affect the cursor's
+        behavior.
+        The <literal>SCROLL</literal> and <literal>NO SCROLL</literal>
+        options have the same meanings as for a bound cursor.
+       </para>
+
+       <para>
+        An example:
+<programlisting>
+OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
+</programlisting>
+       </para>
+     </sect3>
+
+    <sect3 id="plpgsql-cursor-opening-open-for-execute">
+     <title><command>OPEN FOR EXECUTE</command></title>
+
+<synopsis>
+OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>
+                                     <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
+</synopsis>
+
+         <para>
+          The cursor variable is opened and given the specified query to
+          execute.  The cursor cannot be open already, and it must have been
+          declared as an unbound cursor variable (that is, as a simple
+          <type>refcursor</type> variable).  The query is specified as a string
+          expression, in the same way as in the <command>EXECUTE</command>
+          command.  As usual, this gives flexibility so the query plan can vary
+          from one run to the next (see <xref linkend="plpgsql-plan-caching"/>),
+          and it also means that variable substitution is not done on the
+          command string. As with <command>EXECUTE</command>, parameter values
+          can be inserted into the dynamic command via
+          <literal>format()</literal> and <literal>USING</literal>.
+          The <literal>SCROLL</literal> and
+          <literal>NO SCROLL</literal> options have the same meanings as for a bound
+          cursor.
+         </para>
+
+       <para>
+        An example:
+<programlisting>
+OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
+</programlisting>
+        In this example, the table name is inserted into the query via
+        <function>format()</function>.  The comparison value for <literal>col1</literal>
+        is inserted via a <literal>USING</literal> parameter, so it needs
+        no quoting.
+       </para>
+     </sect3>
+
+    <sect3 id="plpgsql-open-bound-cursor">
+     <title>Opening a Bound Cursor</title>
+
+<synopsis>
+OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
+</synopsis>
+
+         <para>
+          This form of <command>OPEN</command> is used to open a cursor
+          variable whose query was bound to it when it was declared.  The
+          cursor cannot be open already.  A list of actual argument value
+          expressions must appear if and only if the cursor was declared to
+          take arguments.  These values will be substituted in the query.
+         </para>
+
+         <para>
+          The query plan for a bound cursor is always considered cacheable;
+          there is no equivalent of <command>EXECUTE</command> in this case.
+          Notice that <literal>SCROLL</literal> and <literal>NO SCROLL</literal> cannot be
+          specified in <command>OPEN</command>, as the cursor's scrolling
+          behavior was already determined.
+         </para>
+
+         <para>
+          Argument values can be passed using either <firstterm>positional</firstterm>
+          or <firstterm>named</firstterm> notation.  In positional
+          notation, all arguments are specified in order.  In named notation,
+          each argument's name is specified using <literal>:=</literal> to
+          separate it from the argument expression. Similar to calling
+          functions, described in <xref linkend="sql-syntax-calling-funcs"/>, it
+          is also allowed to mix positional and named notation.
+         </para>
+
+         <para>
+          Examples (these use the cursor declaration examples above):
+<programlisting>
+OPEN curs2;
+OPEN curs3(42);
+OPEN curs3(key := 42);
+</programlisting>
+         </para>
+
+         <para>
+          Because variable substitution is done on a bound cursor's query,
+          there are really two ways to pass values into the cursor: either
+          with an explicit argument to <command>OPEN</command>, or implicitly by
+          referencing a <application>PL/pgSQL</application> variable in the query.
+          However, only variables declared before the bound cursor was
+          declared will be substituted into it.  In either case the value to
+          be passed is determined at the time of the <command>OPEN</command>.
+          For example, another way to get the same effect as the
+          <literal>curs3</literal> example above is
+<programlisting>
+DECLARE
+    key integer;
+    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
+BEGIN
+    key := 42;
+    OPEN curs4;
+</programlisting>
+         </para>
+     </sect3>
+   </sect2>
+
+   <sect2 id="plpgsql-cursor-using">
+    <title>Using Cursors</title>
+
+    <para>
+     Once a cursor has been opened, it can be manipulated with the
+     statements described here.
+    </para>
+
+    <para>
+     These manipulations need not occur in the same function that
+     opened the cursor to begin with.  You can return a <type>refcursor</type>
+     value out of a function and let the caller operate on the cursor.
+     (Internally, a <type>refcursor</type> value is simply the string name
+     of the portal containing the active query for the cursor.  This name
+     can be passed around, assigned to other <type>refcursor</type> variables,
+     and so on, without disturbing the portal.)
+    </para>
+
+    <para>
+     All portals are implicitly closed at transaction end.  Therefore
+     a <type>refcursor</type> value is usable to reference an open cursor
+     only until the end of the transaction.
+    </para>
+
+    <sect3 id="plpgsql-cursor-using-fetch">
+     <title><literal>FETCH</literal></title>
+
+<synopsis>
+FETCH <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
+</synopsis>
+
+    <para>
+     <command>FETCH</command> retrieves the next row from the
+     cursor into a target, which might be a row variable, a record
+     variable, or a comma-separated list of simple variables, just like
+     <command>SELECT INTO</command>.  If there is no next row, the
+     target is set to NULL(s).  As with <command>SELECT
+     INTO</command>, the special variable <literal>FOUND</literal> can
+     be checked to see whether a row was obtained or not.
+    </para>
+
+    <para>
+     The <replaceable>direction</replaceable> clause can be any of the
+     variants allowed in the SQL <xref linkend="sql-fetch"/>
+     command except the ones that can fetch
+     more than one row; namely, it can be
+     <literal>NEXT</literal>,
+     <literal>PRIOR</literal>,
+     <literal>FIRST</literal>,
+     <literal>LAST</literal>,
+     <literal>ABSOLUTE</literal> <replaceable>count</replaceable>,
+     <literal>RELATIVE</literal> <replaceable>count</replaceable>,
+     <literal>FORWARD</literal>, or
+     <literal>BACKWARD</literal>.
+     Omitting <replaceable>direction</replaceable> is the same
+     as specifying <literal>NEXT</literal>.
+     In the forms using a <replaceable>count</replaceable>,
+     the <replaceable>count</replaceable> can be any integer-valued
+     expression (unlike the SQL <command>FETCH</command> command,
+     which only allows an integer constant).
+     <replaceable>direction</replaceable> values that require moving
+     backward are likely to fail unless the cursor was declared or opened
+     with the <literal>SCROLL</literal> option.
+    </para>
+
+    <para>
+     <replaceable>cursor</replaceable> must be the name of a <type>refcursor</type>
+     variable that references an open cursor portal.
+    </para>
+
+    <para>
+     Examples:
+<programlisting>
+FETCH curs1 INTO rowvar;
+FETCH curs2 INTO foo, bar, baz;
+FETCH LAST FROM curs3 INTO x, y;
+FETCH RELATIVE -2 FROM curs4 INTO x;
+</programlisting>
+       </para>
+     </sect3>
+
+    <sect3 id="plpgsql-cursor-using-move">
+     <title><literal>MOVE</literal></title>
+
+<synopsis>
+MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable>;
+</synopsis>
+
+    <para>
+     <command>MOVE</command> repositions a cursor without retrieving
+     any data. <command>MOVE</command> works exactly like the
+     <command>FETCH</command> command, except it only repositions the
+     cursor and does not return the row moved to. As with <command>SELECT
+     INTO</command>, the special variable <literal>FOUND</literal> can
+     be checked to see whether there was a next row to move to.
+    </para>
+
+    <para>
+     Examples:
+<programlisting>
+MOVE curs1;
+MOVE LAST FROM curs3;
+MOVE RELATIVE -2 FROM curs4;
+MOVE FORWARD 2 FROM curs4;
+</programlisting>
+       </para>
+     </sect3>
+
+    <sect3 id="plpgsql-cursor-using-update-delete">
+     <title><literal>UPDATE/DELETE WHERE CURRENT OF</literal></title>
+
+<synopsis>
+UPDATE <replaceable>table</replaceable> SET ... WHERE CURRENT OF <replaceable>cursor</replaceable>;
+DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>cursor</replaceable>;
+</synopsis>
+
+       <para>
+        When a cursor is positioned on a table row, that row can be updated
+        or deleted using the cursor to identify the row.  There are
+        restrictions on what the cursor's query can be (in particular,
+        no grouping) and it's best to use <literal>FOR UPDATE</literal> in the
+        cursor.  For more information see the
+        <xref linkend="sql-declare"/>
+        reference page.
+       </para>
+
+       <para>
+        An example:
+<programlisting>
+UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
+</programlisting>
+       </para>
+     </sect3>
+
+    <sect3 id="plpgsql-cursor-using-close">
+     <title><literal>CLOSE</literal></title>
+
+<synopsis>
+CLOSE <replaceable>cursor</replaceable>;
+</synopsis>
+
+       <para>
+        <command>CLOSE</command> closes the portal underlying an open
+        cursor.  This can be used to release resources earlier than end of
+        transaction, or to free up the cursor variable to be opened again.
+       </para>
+
+       <para>
+        An example:
+<programlisting>
+CLOSE curs1;
+</programlisting>
+       </para>
+     </sect3>
+
+    <sect3 id="plpgsql-cursor-returning">
+     <title>Returning Cursors</title>
+
+       <para>
+        <application>PL/pgSQL</application> functions can return cursors to the
+        caller. This is useful to return multiple rows or columns,
+        especially with very large result sets.  To do this, the function
+        opens the cursor and returns the cursor name to the caller (or simply
+        opens the cursor using a portal name specified by or otherwise known
+        to the caller).  The caller can then fetch rows from the cursor. The
+        cursor can be closed by the caller, or it will be closed automatically
+        when the transaction closes.
+       </para>
+
+       <para>
+        The portal name used for a cursor can be specified by the
+        programmer or automatically generated.  To specify a portal name,
+        simply assign a string to the <type>refcursor</type> variable before
+        opening it.  The string value of the <type>refcursor</type> variable
+        will be used by <command>OPEN</command> as the name of the underlying portal.
+        However, if the <type>refcursor</type> variable's value is null
+        (as it will be by default), then
+        <command>OPEN</command> automatically generates a name that does not
+        conflict with any existing portal, and assigns it to the
+        <type>refcursor</type> variable.
+       </para>
+
+       <note>
+        <para>
+         Prior to <productname>PostgreSQL</productname> 16, bound cursor
+         variables were initialized to contain their own names, rather
+         than being left as null, so that the underlying portal name would
+         be the same as the cursor variable's name by default.  This was
+         changed because it created too much risk of conflicts between
+         similarly-named cursors in different functions.
+        </para>
+       </note>
+
+       <para>
+        The following example shows one way a cursor name can be supplied by
+        the caller:
+
+<programlisting>
+CREATE TABLE test (col text);
+INSERT INTO test VALUES ('123');
+
+CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
+BEGIN
+    OPEN $1 FOR SELECT col FROM test;
+    RETURN $1;
+END;
+' LANGUAGE plpgsql;
+
+BEGIN;
+SELECT reffunc('funccursor');
+FETCH ALL IN funccursor;
+COMMIT;
+</programlisting>
+       </para>
+
+       <para>
+        The following example uses automatic cursor name generation:
+
+<programlisting>
+CREATE FUNCTION reffunc2() RETURNS refcursor AS '
+DECLARE
+    ref refcursor;
+BEGIN
+    OPEN ref FOR SELECT col FROM test;
+    RETURN ref;
+END;
+' LANGUAGE plpgsql;
+
+-- need to be in a transaction to use cursors.
+BEGIN;
+SELECT reffunc2();
+
+      reffunc2
+--------------------
+ &lt;unnamed cursor 1&gt;
+(1 row)
+
+FETCH ALL IN "&lt;unnamed cursor 1&gt;";
+COMMIT;
+</programlisting>
+       </para>
+
+       <para>
+        The following example shows one way to return multiple cursors
+        from a single function:
+
+<programlisting>
+CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
+BEGIN
+    OPEN $1 FOR SELECT * FROM table_1;
+    RETURN NEXT $1;
+    OPEN $2 FOR SELECT * FROM table_2;
+    RETURN NEXT $2;
+END;
+$$ LANGUAGE plpgsql;
+
+-- need to be in a transaction to use cursors.
+BEGIN;
+
+SELECT * FROM myfunc('a', 'b');
+
+FETCH ALL FROM a;
+FETCH ALL FROM b;
+COMMIT;
+</programlisting>
+       </para>
+     </sect3>
+   </sect2>
+
+   <sect2 id="plpgsql-cursor-for-loop">
+    <title>Looping through a Cursor's Result</title>
+
+    <para>
+     There is a variant of the <command>FOR</command> statement that allows
+     iterating through the rows returned by a cursor.  The syntax is:
+
+<synopsis>
+<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
+FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional> LOOP
+    <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+
+     The cursor variable must have been bound to some query when it was
+     declared, and it <emphasis>cannot</emphasis> be open already.  The
+     <command>FOR</command> statement automatically opens the cursor, and it closes
+     the cursor again when the loop exits.  A list of actual argument value
+     expressions must appear if and only if the cursor was declared to take
+     arguments.  These values will be substituted in the query, in just
+     the same way as during an <command>OPEN</command> (see <xref
+     linkend="plpgsql-open-bound-cursor"/>).
+   </para>
+
+   <para>
+     The variable <replaceable>recordvar</replaceable> is automatically
+     defined as type <type>record</type> and exists only inside the loop (any
+     existing definition of the variable name is ignored within the loop).
+     Each row returned by the cursor is successively assigned to this
+     record variable and the loop body is executed.
+    </para>
+   </sect2>
+
+  </sect1>
+
+  <sect1 id="plpgsql-transactions">
+   <title>Transaction Management</title>
+
+   <para>
+    In procedures invoked by the <command>CALL</command> command
+    as well as in anonymous code blocks (<command>DO</command> command),
+    it is possible to end transactions using the
+    commands <command>COMMIT</command> and <command>ROLLBACK</command>.  A new
+    transaction is started automatically after a transaction is ended using
+    these commands, so there is no separate <command>START
+    TRANSACTION</command> command.  (Note that <command>BEGIN</command> and
+    <command>END</command> have different meanings in PL/pgSQL.)
+   </para>
+
+   <para>
+    Here is a simple example:
+<programlisting>
+CREATE PROCEDURE transaction_test1()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    FOR i IN 0..9 LOOP
+        INSERT INTO test1 (a) VALUES (i);
+        IF i % 2 = 0 THEN
+            COMMIT;
+        ELSE
+            ROLLBACK;
+        END IF;
+    END LOOP;
+END;
+$$;
+
+CALL transaction_test1();
+</programlisting>
+   </para>
+
+   <indexterm zone="plpgsql-transaction-chain">
+    <primary>chained transactions</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <para id="plpgsql-transaction-chain">
+    A new transaction starts out with default transaction characteristics such
+    as transaction isolation level.  In cases where transactions are committed
+    in a loop, it might be desirable to start new transactions automatically
+    with the same characteristics as the previous one.  The commands
+    <command>COMMIT AND CHAIN</command> and <command>ROLLBACK AND
+    CHAIN</command> accomplish this.
+   </para>
+
+   <para>
+    Transaction control is only possible in <command>CALL</command> or
+    <command>DO</command> invocations from the top level or nested
+    <command>CALL</command> or <command>DO</command> invocations without any
+    other intervening command.  For example, if the call stack is
+    <command>CALL proc1()</command> &rarr; <command>CALL proc2()</command>
+    &rarr; <command>CALL proc3()</command>, then the second and third
+    procedures can perform transaction control actions.  But if the call stack
+    is <command>CALL proc1()</command> &rarr; <command>SELECT
+    func2()</command> &rarr; <command>CALL proc3()</command>, then the last
+    procedure cannot do transaction control, because of the
+    <command>SELECT</command> in between.
+   </para>
+
+   <para>
+    Special considerations apply to cursor loops.  Consider this example:
+<programlisting>
+CREATE PROCEDURE transaction_test2()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    r RECORD;
+BEGIN
+    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
+        INSERT INTO test1 (a) VALUES (r.x);
+        COMMIT;
+    END LOOP;
+END;
+$$;
+
+CALL transaction_test2();
+</programlisting>
+    Normally, cursors are automatically closed at transaction commit.
+    However, a cursor created as part of a loop like this is automatically
+    converted to a holdable cursor by the first <command>COMMIT</command> or
+    <command>ROLLBACK</command>.  That means that the cursor is fully
+    evaluated at the first <command>COMMIT</command> or
+    <command>ROLLBACK</command> rather than row by row.  The cursor is still
+    removed automatically after the loop, so this is mostly invisible to the
+    user.
+   </para>
+
+   <para>
+    Transaction commands are not allowed in cursor loops driven by commands
+    that are not read-only (for example <command>UPDATE
+    ... RETURNING</command>).
+   </para>
+
+   <para>
+    A transaction cannot be ended inside a block with exception handlers.
+   </para>
+  </sect1>
+
+  <sect1 id="plpgsql-errors-and-messages">
+   <title>Errors and Messages</title>
+
+  <sect2 id="plpgsql-statements-raise">
+   <title>Reporting Errors and Messages</title>
+
+   <indexterm>
+    <primary>RAISE</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <indexterm>
+    <primary>reporting errors</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <para>
+    Use the <command>RAISE</command> statement to report messages and
+    raise errors.
+
+<synopsis>
+RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ... </optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
+RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</replaceable> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
+RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</replaceable>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
+RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;
+RAISE ;
+</synopsis>
+
+    The <replaceable class="parameter">level</replaceable> option specifies
+    the error severity.  Allowed levels are <literal>DEBUG</literal>,
+    <literal>LOG</literal>, <literal>INFO</literal>,
+    <literal>NOTICE</literal>, <literal>WARNING</literal>,
+    and <literal>EXCEPTION</literal>, with <literal>EXCEPTION</literal>
+    being the default.
+    <literal>EXCEPTION</literal> raises an error (which normally aborts the
+    current transaction); the other levels only generate messages of different
+    priority levels.
+    Whether messages of a particular priority are reported to the client,
+    written to the server log, or both is controlled by the
+    <xref linkend="guc-log-min-messages"/> and
+    <xref linkend="guc-client-min-messages"/> configuration
+    variables. See <xref linkend="runtime-config"/> for more
+    information.
+   </para>
+
+   <para>
+    After <replaceable class="parameter">level</replaceable> if any,
+    you can specify a <replaceable class="parameter">format</replaceable> string
+    (which must be a simple string literal, not an expression).  The
+    format string specifies the error message text to be reported.
+    The format string can be followed
+    by optional argument expressions to be inserted into the message.
+    Inside the format string, <literal>%</literal> is replaced by the
+    string representation of the next optional argument's value. Write
+    <literal>%%</literal> to emit a literal <literal>%</literal>.
+    The number of arguments must match the number of <literal>%</literal>
+    placeholders in the format string, or an error is raised during
+    the compilation of the function.
+   </para>
+
+   <para>
+    In this example, the value of <literal>v_job_id</literal> will replace the
+    <literal>%</literal> in the string:
+<programlisting>
+RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
+</programlisting>
+   </para>
+
+   <para>
+    You can attach additional information to the error report by writing
+    <literal>USING</literal> followed by <replaceable
+    class="parameter">option</replaceable> = <replaceable
+    class="parameter">expression</replaceable> items.  Each
+    <replaceable class="parameter">expression</replaceable> can be any
+    string-valued expression.  The allowed <replaceable
+    class="parameter">option</replaceable> key words are:
+
+    <variablelist id="raise-using-options">
+     <varlistentry id="raise-using-option-message">
+      <term><literal>MESSAGE</literal></term>
+      <listitem>
+       <para>Sets the error message text.  This option can't be used in the
+        form of <command>RAISE</command> that includes a format string
+        before <literal>USING</literal>.</para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="raise-using-option-detail">
+      <term><literal>DETAIL</literal></term>
+      <listitem>
+       <para>Supplies an error detail message.</para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="raise-using-option-hint">
+      <term><literal>HINT</literal></term>
+      <listitem>
+       <para>Supplies a hint message.</para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="raise-using-option-errcode">
+      <term><literal>ERRCODE</literal></term>
+      <listitem>
+       <para>Specifies the error code (SQLSTATE) to report, either by condition
+        name, as shown in <xref linkend="errcodes-appendix"/>, or directly as a
+        five-character SQLSTATE code.</para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="raise-using-option-column">
+      <term><literal>COLUMN</literal></term>
+      <term><literal>CONSTRAINT</literal></term>
+      <term><literal>DATATYPE</literal></term>
+      <term><literal>TABLE</literal></term>
+      <term><literal>SCHEMA</literal></term>
+      <listitem>
+       <para>Supplies the name of a related object.</para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+
+   <para>
+    This example will abort the transaction with the given error message
+    and hint:
+<programlisting>
+RAISE EXCEPTION 'Nonexistent ID --> %', user_id
+      USING HINT = 'Please check your user ID';
+</programlisting>
+   </para>
+
+   <para>
+    These two examples show equivalent ways of setting the SQLSTATE:
+<programlisting>
+RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
+RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
+</programlisting>
+   </para>
+
+   <para>
+    There is a second <command>RAISE</command> syntax in which the main argument
+    is the condition name or SQLSTATE to be reported, for example:
+<programlisting>
+RAISE division_by_zero;
+RAISE SQLSTATE '22012';
+</programlisting>
+    In this syntax, <literal>USING</literal> can be used to supply a custom
+    error message, detail, or hint.  Another way to do the earlier
+    example is
+<programlisting>
+RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
+</programlisting>
+   </para>
+
+   <para>
+    Still another variant is to write <literal>RAISE USING</literal> or <literal>RAISE
+    <replaceable class="parameter">level</replaceable> USING</literal> and put
+    everything else into the <literal>USING</literal> list.
+   </para>
+
+   <para>
+    The last variant of <command>RAISE</command> has no parameters at all.
+    This form can only be used inside a <literal>BEGIN</literal> block's
+    <literal>EXCEPTION</literal> clause;
+    it causes the error currently being handled to be re-thrown.
+   </para>
+
+   <note>
+    <para>
+     Before <productname>PostgreSQL</productname> 9.1, <command>RAISE</command> without
+     parameters was interpreted as re-throwing the error from the block
+     containing the active exception handler.  Thus an <literal>EXCEPTION</literal>
+     clause nested within that handler could not catch it, even if the
+     <command>RAISE</command> was within the nested <literal>EXCEPTION</literal> clause's
+     block. This was deemed surprising as well as being incompatible with
+     Oracle's PL/SQL.
+    </para>
+   </note>
+
+   <para>
+    If no condition name nor SQLSTATE is specified in a
+    <command>RAISE EXCEPTION</command> command, the default is to use
+    <literal>raise_exception</literal> (<literal>P0001</literal>).
+    If no message text is specified, the default is to use the condition
+    name or SQLSTATE as message text.
+   </para>
+
+   <note>
+    <para>
+     When specifying an error code by SQLSTATE code, you are not
+     limited to the predefined error codes, but can select any
+     error code consisting of five digits and/or upper-case ASCII
+     letters, other than <literal>00000</literal>.  It is recommended that
+     you avoid throwing error codes that end in three zeroes, because
+     these are category codes and can only be trapped by trapping
+     the whole category.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="plpgsql-statements-assert">
+   <title>Checking Assertions</title>
+
+   <indexterm>
+    <primary>ASSERT</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <indexterm>
+    <primary>assertions</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <indexterm>
+    <primary><varname>plpgsql.check_asserts</varname> configuration parameter</primary>
+   </indexterm>
+
+   <para>
+    The <command>ASSERT</command> statement is a convenient shorthand for
+    inserting debugging checks into <application>PL/pgSQL</application>
+    functions.
+
+<synopsis>
+ASSERT <replaceable class="parameter">condition</replaceable> <optional> , <replaceable class="parameter">message</replaceable> </optional>;
+</synopsis>
+
+    The <replaceable class="parameter">condition</replaceable> is a Boolean
+    expression that is expected to always evaluate to true; if it does,
+    the <command>ASSERT</command> statement does nothing further.  If the
+    result is false or null, then an <literal>ASSERT_FAILURE</literal> exception
+    is raised.  (If an error occurs while evaluating
+    the <replaceable class="parameter">condition</replaceable>, it is
+    reported as a normal error.)
+   </para>
+
+   <para>
+    If the optional <replaceable class="parameter">message</replaceable> is
+    provided, it is an expression whose result (if not null) replaces the
+    default error message text <quote>assertion failed</quote>, should
+    the <replaceable class="parameter">condition</replaceable> fail.
+    The <replaceable class="parameter">message</replaceable> expression is
+    not evaluated in the normal case where the assertion succeeds.
+   </para>
+
+   <para>
+    Testing of assertions can be enabled or disabled via the configuration
+    parameter <literal>plpgsql.check_asserts</literal>, which takes a Boolean
+    value; the default is <literal>on</literal>.  If this parameter
+    is <literal>off</literal> then <command>ASSERT</command> statements do nothing.
+   </para>
+
+   <para>
+    Note that <command>ASSERT</command> is meant for detecting program
+    bugs, not for reporting ordinary error conditions.  Use
+    the <command>RAISE</command> statement, described above, for that.
+   </para>
+
+  </sect2>
+
+ </sect1>
+
+ <sect1 id="plpgsql-trigger">
+  <title>Trigger Functions</title>
+
+  <indexterm zone="plpgsql-trigger">
+   <primary>trigger</primary>
+   <secondary>in PL/pgSQL</secondary>
+  </indexterm>
+
+  <para>
+   <application>PL/pgSQL</application> can be used to define trigger
+   functions on data changes or database events.
+   A trigger function is created with the <command>CREATE FUNCTION</command>
+   command, declaring it as a function with no arguments and a return type of
+   <type>trigger</type> (for data change triggers) or
+   <type>event_trigger</type> (for database event triggers).
+   Special local variables named <varname>TG_<replaceable>something</replaceable></varname> are
+   automatically defined to describe the condition that triggered the call.
+  </para>
+
+  <sect2 id="plpgsql-dml-trigger">
+   <title>Triggers on Data Changes</title>
+
+  <para>
+   A <link linkend="triggers">data change trigger</link> is declared as a
+   function with no arguments and a return type of <type>trigger</type>.
+   Note that the function must be declared with no arguments even if it
+   expects to receive some arguments specified in <command>CREATE TRIGGER</command>
+   &mdash; such arguments are passed via <varname>TG_ARGV</varname>, as described
+   below.
+  </para>
+
+  <para>
+   When a <application>PL/pgSQL</application> function is called as a
+   trigger, several special variables are created automatically in the
+   top-level block. They are:
+
+   <variablelist>
+    <varlistentry id="plpgsql-dml-trigger-new">
+     <term><varname>NEW</varname> <type>record</type></term>
+     <listitem>
+      <para>
+       new database row for <command>INSERT</command>/<command>UPDATE</command> operations in row-level
+       triggers. This variable is null in statement-level triggers
+       and for <command>DELETE</command> operations.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-old">
+     <term><varname>OLD</varname> <type>record</type></term>
+     <listitem>
+      <para>
+       old database row for <command>UPDATE</command>/<command>DELETE</command> operations in row-level
+       triggers. This variable is null in statement-level triggers
+       and for <command>INSERT</command> operations.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-name">
+     <term><varname>TG_NAME</varname> <type>name</type></term>
+     <listitem>
+      <para>
+       name of the trigger which fired.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-when">
+     <term><varname>TG_WHEN</varname> <type>text</type></term>
+     <listitem>
+      <para>
+       <literal>BEFORE</literal>, <literal>AFTER</literal>, or
+       <literal>INSTEAD OF</literal>, depending on the trigger's definition.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-level">
+     <term><varname>TG_LEVEL</varname> <type>text</type></term>
+     <listitem>
+      <para>
+       <literal>ROW</literal> or <literal>STATEMENT</literal>,
+       depending on the trigger's definition.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-op">
+     <term><varname>TG_OP</varname> <type>text</type></term>
+     <listitem>
+      <para>
+       operation for which the trigger was fired:
+       <literal>INSERT</literal>, <literal>UPDATE</literal>,
+       <literal>DELETE</literal>, or <literal>TRUNCATE</literal>.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-relid">
+     <term><varname>TG_RELID</varname> <type>oid</type> (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)</term>
+     <listitem>
+      <para>
+       object ID of the table that caused the trigger invocation.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-relname">
+     <term><varname>TG_RELNAME</varname> <type>name</type></term>
+     <listitem>
+      <para>
+       table that caused the trigger
+       invocation. This is now deprecated, and could disappear in a future
+       release. Use <literal>TG_TABLE_NAME</literal> instead.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-table-name">
+     <term><varname>TG_TABLE_NAME</varname> <type>name</type></term>
+     <listitem>
+      <para>
+       table that caused the trigger invocation.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-table-schema">
+     <term><varname>TG_TABLE_SCHEMA</varname> <type>name</type></term>
+     <listitem>
+      <para>
+       schema of the table that caused the trigger invocation.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-nargs">
+     <term><varname>TG_NARGS</varname> <type>integer</type></term>
+     <listitem>
+      <para>
+       number of arguments given to the trigger
+       function in the <command>CREATE TRIGGER</command> statement.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-argv">
+     <term><varname>TG_ARGV</varname> <type>text[]</type></term>
+     <listitem>
+      <para>
+       arguments from
+       the <command>CREATE TRIGGER</command> statement.
+       The index counts from 0. Invalid
+       indexes (less than 0 or greater than or equal to <varname>tg_nargs</varname>)
+       result in a null value.
+      </para>
+     </listitem>
+    </varlistentry>
+   </variablelist>
+  </para>
+
+   <para>
+    A trigger function must return either <symbol>NULL</symbol> or a
+    record/row value having exactly the structure of the table the
+    trigger was fired for.
+   </para>
+
+   <para>
+    Row-level triggers fired <literal>BEFORE</literal> can return null to signal the
+    trigger manager to skip the rest of the operation for this row
+    (i.e., subsequent triggers are not fired, and the
+    <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> does not occur
+    for this row).  If a nonnull
+    value is returned then the operation proceeds with that row value.
+    Returning a row value different from the original value
+    of <varname>NEW</varname> alters the row that will be inserted or
+    updated.  Thus, if the trigger function wants the triggering
+    action to succeed normally without altering the row
+    value, <varname>NEW</varname> (or a value equal thereto) has to be
+    returned.  To alter the row to be stored, it is possible to
+    replace single values directly in <varname>NEW</varname> and return the
+    modified <varname>NEW</varname>, or to build a complete new record/row to
+    return.  In the case of a before-trigger
+    on <command>DELETE</command>, the returned value has no direct
+    effect, but it has to be nonnull to allow the trigger action to
+    proceed.  Note that <varname>NEW</varname> is null
+    in <command>DELETE</command> triggers, so returning that is
+    usually not sensible.  The usual idiom in <command>DELETE</command>
+    triggers is to return <varname>OLD</varname>.
+   </para>
+
+   <para>
+    <literal>INSTEAD OF</literal> triggers (which are always row-level triggers,
+    and may only be used on views) can return null to signal that they did
+    not perform any updates, and that the rest of the operation for this
+    row should be skipped (i.e., subsequent triggers are not fired, and the
+    row is not counted in the rows-affected status for the surrounding
+    <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>).
+    Otherwise a nonnull value should be returned, to signal
+    that the trigger performed the requested operation. For
+    <command>INSERT</command> and <command>UPDATE</command> operations, the return value
+    should be <varname>NEW</varname>, which the trigger function may modify to
+    support <command>INSERT RETURNING</command> and <command>UPDATE RETURNING</command>
+    (this will also affect the row value passed to any subsequent triggers,
+    or passed to a special <varname>EXCLUDED</varname> alias reference within
+    an <command>INSERT</command> statement with an <literal>ON CONFLICT DO
+    UPDATE</literal> clause).  For <command>DELETE</command> operations, the return
+    value should be <varname>OLD</varname>.
+   </para>
+
+   <para>
+    The return value of a row-level trigger
+    fired <literal>AFTER</literal> or a statement-level trigger
+    fired <literal>BEFORE</literal> or <literal>AFTER</literal> is
+    always ignored; it might as well be null. However, any of these types of
+    triggers might still abort the entire operation by raising an error.
+   </para>
+
+   <para>
+    <xref linkend="plpgsql-trigger-example"/> shows an example of a
+    trigger function in <application>PL/pgSQL</application>.
+   </para>
+
+   <example id="plpgsql-trigger-example">
+    <title>A <application>PL/pgSQL</application> Trigger Function</title>
+
+    <para>
+     This example trigger ensures that any time a row is inserted or updated
+     in the table, the current user name and time are stamped into the
+     row. And it checks that an employee's name is given and that the
+     salary is a positive value.
+    </para>
+
+<programlisting>
+CREATE TABLE emp (
+    empname           text,
+    salary            integer,
+    last_date         timestamp,
+    last_user         text
+);
+
+CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.empname IS NULL THEN
+            RAISE EXCEPTION 'empname cannot be null';
+        END IF;
+        IF NEW.salary IS NULL THEN
+            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
+        END IF;
+
+        -- Who works for us when they must pay for it?
+        IF NEW.salary &lt; 0 THEN
+            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
+        END IF;
+
+        -- Remember who changed the payroll when
+        NEW.last_date := current_timestamp;
+        NEW.last_user := current_user;
+        RETURN NEW;
+    END;
+$emp_stamp$ LANGUAGE plpgsql;
+
+CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
+    FOR EACH ROW EXECUTE FUNCTION emp_stamp();
+</programlisting>
+   </example>
+
+   <para>
+    Another way to log changes to a table involves creating a new table that
+    holds a row for each insert, update, or delete that occurs. This approach
+    can be thought of as auditing changes to a table.
+    <xref linkend="plpgsql-trigger-audit-example"/> shows an example of an
+    audit trigger function in <application>PL/pgSQL</application>.
+   </para>
+
+   <example id="plpgsql-trigger-audit-example">
+    <title>A <application>PL/pgSQL</application> Trigger Function for Auditing</title>
+
+    <para>
+     This example trigger ensures that any insert, update or delete of a row
+     in the <literal>emp</literal> table is recorded (i.e., audited) in the <literal>emp_audit</literal> table.
+     The current time and user name are stamped into the row, together with
+     the type of operation performed on it.
+    </para>
+
+<programlisting>
+CREATE TABLE emp (
+    empname           text NOT NULL,
+    salary            integer
+);
+
+CREATE TABLE emp_audit(
+    operation         char(1)   NOT NULL,
+    stamp             timestamp NOT NULL,
+    userid            text      NOT NULL,
+    empname           text      NOT NULL,
+    salary            integer
+);
+
+CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
+    BEGIN
+        --
+        -- Create a row in emp_audit to reflect the operation performed on emp,
+        -- making use of the special variable TG_OP to work out the operation.
+        --
+        IF (TG_OP = 'DELETE') THEN
+            INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
+        ELSIF (TG_OP = 'UPDATE') THEN
+            INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
+        ELSIF (TG_OP = 'INSERT') THEN
+            INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
+        END IF;
+        RETURN NULL; -- result is ignored since this is an AFTER trigger
+    END;
+$emp_audit$ LANGUAGE plpgsql;
+
+CREATE TRIGGER emp_audit
+AFTER INSERT OR UPDATE OR DELETE ON emp
+    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();
+</programlisting>
+   </example>
+
+   <para>
+    A variation of the previous example uses a view joining the main table
+    to the audit table, to show when each entry was last modified. This
+    approach still records the full audit trail of changes to the table,
+    but also presents a simplified view of the audit trail, showing just
+    the last modified timestamp derived from the audit trail for each entry.
+    <xref linkend="plpgsql-view-trigger-audit-example"/> shows an example
+    of an audit trigger on a view in <application>PL/pgSQL</application>.
+   </para>
+
+   <example id="plpgsql-view-trigger-audit-example">
+    <title>A <application>PL/pgSQL</application> View Trigger Function for Auditing</title>
+
+    <para>
+     This example uses a trigger on the view to make it updatable, and
+     ensure that any insert, update or delete of a row in the view is
+     recorded (i.e., audited) in the <literal>emp_audit</literal> table. The current time
+     and user name are recorded, together with the type of operation
+     performed, and the view displays the last modified time of each row.
+    </para>
+
+<programlisting>
+CREATE TABLE emp (
+    empname           text PRIMARY KEY,
+    salary            integer
+);
+
+CREATE TABLE emp_audit(
+    operation         char(1)   NOT NULL,
+    userid            text      NOT NULL,
+    empname           text      NOT NULL,
+    salary            integer,
+    stamp             timestamp NOT NULL
+);
+
+CREATE VIEW emp_view AS
+    SELECT e.empname,
+           e.salary,
+           max(ea.stamp) AS last_updated
+      FROM emp e
+      LEFT JOIN emp_audit ea ON ea.empname = e.empname
+     GROUP BY 1, 2;
+
+CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
+    BEGIN
+        --
+        -- Perform the required operation on emp, and create a row in emp_audit
+        -- to reflect the change made to emp.
+        --
+        IF (TG_OP = 'DELETE') THEN
+            DELETE FROM emp WHERE empname = OLD.empname;
+            IF NOT FOUND THEN RETURN NULL; END IF;
+
+            OLD.last_updated = now();
+            INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
+            RETURN OLD;
+        ELSIF (TG_OP = 'UPDATE') THEN
+            UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
+            IF NOT FOUND THEN RETURN NULL; END IF;
+
+            NEW.last_updated = now();
+            INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
+            RETURN NEW;
+        ELSIF (TG_OP = 'INSERT') THEN
+            INSERT INTO emp VALUES(NEW.empname, NEW.salary);
+
+            NEW.last_updated = now();
+            INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
+            RETURN NEW;
+        END IF;
+    END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER emp_audit
+INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
+    FOR EACH ROW EXECUTE FUNCTION update_emp_view();
+</programlisting>
+   </example>
+
+   <para>
+    One use of triggers is to maintain a summary table
+    of another table. The resulting summary can be used in place of the
+    original table for certain queries &mdash; often with vastly reduced run
+    times.
+    This technique is commonly used in Data Warehousing, where the tables
+    of measured or observed data (called fact tables) might be extremely large.
+    <xref linkend="plpgsql-trigger-summary-example"/> shows an example of a
+    trigger function in <application>PL/pgSQL</application> that maintains
+    a summary table for a fact table in a data warehouse.
+   </para>
+
+
+   <example id="plpgsql-trigger-summary-example">
+    <title>A <application>PL/pgSQL</application> Trigger Function for Maintaining a Summary Table</title>
+
+    <para>
+     The schema detailed here is partly based on the <emphasis>Grocery Store
+     </emphasis> example from <emphasis>The Data Warehouse Toolkit</emphasis>
+     by Ralph Kimball.
+    </para>
+
+<programlisting>
+--
+-- Main tables - time dimension and sales fact.
+--
+CREATE TABLE time_dimension (
+    time_key                    integer NOT NULL,
+    day_of_week                 integer NOT NULL,
+    day_of_month                integer NOT NULL,
+    month                       integer NOT NULL,
+    quarter                     integer NOT NULL,
+    year                        integer NOT NULL
+);
+CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
+
+CREATE TABLE sales_fact (
+    time_key                    integer NOT NULL,
+    product_key                 integer NOT NULL,
+    store_key                   integer NOT NULL,
+    amount_sold                 numeric(12,2) NOT NULL,
+    units_sold                  integer NOT NULL,
+    amount_cost                 numeric(12,2) NOT NULL
+);
+CREATE INDEX sales_fact_time ON sales_fact(time_key);
+
+--
+-- Summary table - sales by time.
+--
+CREATE TABLE sales_summary_bytime (
+    time_key                    integer NOT NULL,
+    amount_sold                 numeric(15,2) NOT NULL,
+    units_sold                  numeric(12) NOT NULL,
+    amount_cost                 numeric(15,2) NOT NULL
+);
+CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
+
+--
+-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
+--
+CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
+AS $maint_sales_summary_bytime$
+    DECLARE
+        delta_time_key          integer;
+        delta_amount_sold       numeric(15,2);
+        delta_units_sold        numeric(12);
+        delta_amount_cost       numeric(15,2);
+    BEGIN
+
+        -- Work out the increment/decrement amount(s).
+        IF (TG_OP = 'DELETE') THEN
+
+            delta_time_key = OLD.time_key;
+            delta_amount_sold = -1 * OLD.amount_sold;
+            delta_units_sold = -1 * OLD.units_sold;
+            delta_amount_cost = -1 * OLD.amount_cost;
+
+        ELSIF (TG_OP = 'UPDATE') THEN
+
+            -- forbid updates that change the time_key -
+            -- (probably not too onerous, as DELETE + INSERT is how most
+            -- changes will be made).
+            IF ( OLD.time_key != NEW.time_key) THEN
+                RAISE EXCEPTION 'Update of time_key : % -&gt; % not allowed',
+                                                      OLD.time_key, NEW.time_key;
+            END IF;
+
+            delta_time_key = OLD.time_key;
+            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
+            delta_units_sold = NEW.units_sold - OLD.units_sold;
+            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
+
+        ELSIF (TG_OP = 'INSERT') THEN
+
+            delta_time_key = NEW.time_key;
+            delta_amount_sold = NEW.amount_sold;
+            delta_units_sold = NEW.units_sold;
+            delta_amount_cost = NEW.amount_cost;
+
+        END IF;
+
+
+        -- Insert or update the summary row with the new values.
+        &lt;&lt;insert_update&gt;&gt;
+        LOOP
+            UPDATE sales_summary_bytime
+                SET amount_sold = amount_sold + delta_amount_sold,
+                    units_sold = units_sold + delta_units_sold,
+                    amount_cost = amount_cost + delta_amount_cost
+                WHERE time_key = delta_time_key;
+
+            EXIT insert_update WHEN found;
+
+            BEGIN
+                INSERT INTO sales_summary_bytime (
+                            time_key,
+                            amount_sold,
+                            units_sold,
+                            amount_cost)
+                    VALUES (
+                            delta_time_key,
+                            delta_amount_sold,
+                            delta_units_sold,
+                            delta_amount_cost
+                           );
+
+                EXIT insert_update;
+
+            EXCEPTION
+                WHEN UNIQUE_VIOLATION THEN
+                    -- do nothing
+            END;
+        END LOOP insert_update;
+
+        RETURN NULL;
+
+    END;
+$maint_sales_summary_bytime$ LANGUAGE plpgsql;
+
+CREATE TRIGGER maint_sales_summary_bytime
+AFTER INSERT OR UPDATE OR DELETE ON sales_fact
+    FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
+
+INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
+INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
+INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
+INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
+SELECT * FROM sales_summary_bytime;
+DELETE FROM sales_fact WHERE product_key = 1;
+SELECT * FROM sales_summary_bytime;
+UPDATE sales_fact SET units_sold = units_sold * 2;
+SELECT * FROM sales_summary_bytime;
+</programlisting>
+   </example>
+
+   <para>
+    <literal>AFTER</literal> triggers can also make use of <firstterm>transition
+    tables</firstterm> to inspect the entire set of rows changed by the triggering
+    statement.  The <command>CREATE TRIGGER</command> command assigns names to one
+    or both transition tables, and then the function can refer to those names
+    as though they were read-only temporary tables.
+    <xref linkend="plpgsql-trigger-audit-transition-example"/> shows an example.
+   </para>
+
+   <example id="plpgsql-trigger-audit-transition-example">
+    <title>Auditing with Transition Tables</title>
+
+    <para>
+     This example produces the same results as
+     <xref linkend="plpgsql-trigger-audit-example"/>, but instead of using a
+     trigger that fires for every row, it uses a trigger that fires once
+     per statement, after collecting the relevant information in a transition
+     table.  This can be significantly faster than the row-trigger approach
+     when the invoking statement has modified many rows.  Notice that we must
+     make a separate trigger declaration for each kind of event, since the
+     <literal>REFERENCING</literal> clauses must be different for each case.  But
+     this does not stop us from using a single trigger function if we choose.
+     (In practice, it might be better to use three separate functions and
+     avoid the run-time tests on <varname>TG_OP</varname>.)
+    </para>
+
+<programlisting>
+CREATE TABLE emp (
+    empname           text NOT NULL,
+    salary            integer
+);
+
+CREATE TABLE emp_audit(
+    operation         char(1)   NOT NULL,
+    stamp             timestamp NOT NULL,
+    userid            text      NOT NULL,
+    empname           text      NOT NULL,
+    salary            integer
+);
+
+CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
+    BEGIN
+        --
+        -- Create rows in emp_audit to reflect the operations performed on emp,
+        -- making use of the special variable TG_OP to work out the operation.
+        --
+        IF (TG_OP = 'DELETE') THEN
+            INSERT INTO emp_audit
+                SELECT 'D', now(), current_user, o.* FROM old_table o;
+        ELSIF (TG_OP = 'UPDATE') THEN
+            INSERT INTO emp_audit
+                SELECT 'U', now(), current_user, n.* FROM new_table n;
+        ELSIF (TG_OP = 'INSERT') THEN
+            INSERT INTO emp_audit
+                SELECT 'I', now(), current_user, n.* FROM new_table n;
+        END IF;
+        RETURN NULL; -- result is ignored since this is an AFTER trigger
+    END;
+$emp_audit$ LANGUAGE plpgsql;
+
+CREATE TRIGGER emp_audit_ins
+    AFTER INSERT ON emp
+    REFERENCING NEW TABLE AS new_table
+    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
+CREATE TRIGGER emp_audit_upd
+    AFTER UPDATE ON emp
+    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
+    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
+CREATE TRIGGER emp_audit_del
+    AFTER DELETE ON emp
+    REFERENCING OLD TABLE AS old_table
+    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
+</programlisting>
+   </example>
+
+</sect2>
+
+  <sect2 id="plpgsql-event-trigger">
+   <title>Triggers on Events</title>
+
+   <para>
+    <application>PL/pgSQL</application> can be used to define
+    <link linkend="event-triggers">event triggers</link>.
+    <productname>PostgreSQL</productname> requires that a function that
+    is to be called as an event trigger must be declared as a function with
+    no arguments and a return type of <literal>event_trigger</literal>.
+   </para>
+
+   <para>
+    When a <application>PL/pgSQL</application> function is called as an
+    event trigger, several special variables are created automatically
+    in the top-level block. They are:
+
+   <variablelist>
+    <varlistentry id="plpgsql-event-trigger-tg-event">
+     <term><varname>TG_EVENT</varname> <type>text</type></term>
+     <listitem>
+      <para>
+       event the trigger is fired for.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-event-trigger-tg-tag">
+     <term><varname>TG_TAG</varname> <type>text</type></term>
+     <listitem>
+      <para>
+       command tag for which the trigger is fired.
+      </para>
+     </listitem>
+    </varlistentry>
+   </variablelist>
+  </para>
+
+   <para>
+    <xref linkend="plpgsql-event-trigger-example"/> shows an example of an
+    event trigger function in <application>PL/pgSQL</application>.
+   </para>
+
+   <example id="plpgsql-event-trigger-example">
+    <title>A <application>PL/pgSQL</application> Event Trigger Function</title>
+
+    <para>
+     This example trigger simply raises a <literal>NOTICE</literal> message
+     each time a supported command is executed.
+    </para>
+
+<programlisting>
+CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
+</programlisting>
+   </example>
+  </sect2>
+
+  </sect1>
+
+  <sect1 id="plpgsql-implementation">
+   <title><application>PL/pgSQL</application> under the Hood</title>
+
+   <para>
+    This section discusses some implementation details that are
+    frequently important for <application>PL/pgSQL</application> users to know.
+   </para>
+
+  <sect2 id="plpgsql-var-subst">
+   <title>Variable Substitution</title>
+
+   <para>
+    SQL statements and expressions within a <application>PL/pgSQL</application> function
+    can refer to variables and parameters of the function.  Behind the scenes,
+    <application>PL/pgSQL</application> substitutes query parameters for such references.
+    Query parameters will only be substituted in places where they are
+    syntactically permissible.  As an extreme case, consider
+    this example of poor programming style:
+<programlisting>
+INSERT INTO foo (foo) VALUES (foo(foo));
+</programlisting>
+    The first occurrence of <literal>foo</literal> must syntactically be a table
+    name, so it will not be substituted, even if the function has a variable
+    named <literal>foo</literal>.  The second occurrence must be the name of a
+    column of that table, so it will not be substituted either.  Likewise
+    the third occurrence must be a function name, so it also will not be
+    substituted for.  Only the last occurrence is a candidate to be a
+    reference to a variable of the <application>PL/pgSQL</application>
+    function.
+   </para>
+
+   <para>
+    Another way to understand this is that variable substitution can only
+    insert data values into an SQL command; it cannot dynamically change which
+    database objects are referenced by the command.  (If you want to do
+    that, you must build a command string dynamically, as explained in
+    <xref linkend="plpgsql-statements-executing-dyn"/>.)
+   </para>
+
+   <para>
+    Since the names of variables are syntactically no different from the names
+    of table columns, there can be ambiguity in statements that also refer to
+    tables: is a given name meant to refer to a table column, or a variable?
+    Let's change the previous example to
+<programlisting>
+INSERT INTO dest (col) SELECT foo + bar FROM src;
+</programlisting>
+    Here, <literal>dest</literal> and <literal>src</literal> must be table names, and
+    <literal>col</literal> must be a column of <literal>dest</literal>, but <literal>foo</literal>
+    and <literal>bar</literal> might reasonably be either variables of the function
+    or columns of <literal>src</literal>.
+   </para>
+
+   <para>
+    By default, <application>PL/pgSQL</application> will report an error if a name
+    in an SQL statement could refer to either a variable or a table column.
+    You can fix such a problem by renaming the variable or column,
+    or by qualifying the ambiguous reference, or by telling
+    <application>PL/pgSQL</application> which interpretation to prefer.
+   </para>
+
+   <para>
+    The simplest solution is to rename the variable or column.
+    A common coding rule is to use a
+    different naming convention for <application>PL/pgSQL</application>
+    variables than you use for column names.  For example,
+    if you consistently name function variables
+    <literal>v_<replaceable>something</replaceable></literal> while none of your
+    column names start with <literal>v_</literal>, no conflicts will occur.
+   </para>
+
+   <para>
+    Alternatively you can qualify ambiguous references to make them clear.
+    In the above example, <literal>src.foo</literal> would be an unambiguous reference
+    to the table column.  To create an unambiguous reference to a variable,
+    declare it in a labeled block and use the block's label
+    (see <xref linkend="plpgsql-structure"/>).  For example,
+<programlisting>
+&lt;&lt;block&gt;&gt;
+DECLARE
+    foo int;
+BEGIN
+    foo := ...;
+    INSERT INTO dest (col) SELECT block.foo + bar FROM src;
+</programlisting>
+    Here <literal>block.foo</literal> means the variable even if there is a column
+    <literal>foo</literal> in <literal>src</literal>.  Function parameters, as well as
+    special variables such as <literal>FOUND</literal>, can be qualified by the
+    function's name, because they are implicitly declared in an outer block
+    labeled with the function's name.
+   </para>
+
+   <para>
+    Sometimes it is impractical to fix all the ambiguous references in a
+    large body of <application>PL/pgSQL</application> code.  In such cases you can
+    specify that <application>PL/pgSQL</application> should resolve ambiguous references
+    as the variable (which is compatible with <application>PL/pgSQL</application>'s
+    behavior before <productname>PostgreSQL</productname> 9.0), or as the
+    table column (which is compatible with some other systems such as
+    <productname>Oracle</productname>).
+   </para>
+
+   <indexterm>
+     <primary><varname>plpgsql.variable_conflict</varname> configuration parameter</primary>
+   </indexterm>
+
+   <para>
+    To change this behavior on a system-wide basis, set the configuration
+    parameter <literal>plpgsql.variable_conflict</literal> to one of
+    <literal>error</literal>, <literal>use_variable</literal>, or
+    <literal>use_column</literal> (where <literal>error</literal> is the factory default).
+    This parameter affects subsequent compilations
+    of statements in <application>PL/pgSQL</application> functions, but not statements
+    already compiled in the current session.
+    Because changing this setting
+    can cause unexpected changes in the behavior of <application>PL/pgSQL</application>
+    functions, it can only be changed by a superuser.
+   </para>
+
+   <para>
+    You can also set the behavior on a function-by-function basis, by
+    inserting one of these special commands at the start of the function
+    text:
+<programlisting>
+#variable_conflict error
+#variable_conflict use_variable
+#variable_conflict use_column
+</programlisting>
+    These commands affect only the function they are written in, and override
+    the setting of <literal>plpgsql.variable_conflict</literal>.  An example is
+<programlisting>
+CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
+    #variable_conflict use_variable
+    DECLARE
+        curtime timestamp := now();
+    BEGIN
+        UPDATE users SET last_modified = curtime, comment = comment
+          WHERE users.id = id;
+    END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+    In the <literal>UPDATE</literal> command, <literal>curtime</literal>, <literal>comment</literal>,
+    and <literal>id</literal> will refer to the function's variable and parameters
+    whether or not <literal>users</literal> has columns of those names.  Notice
+    that we had to qualify the reference to <literal>users.id</literal> in the
+    <literal>WHERE</literal> clause to make it refer to the table column.
+    But we did not have to qualify the reference to <literal>comment</literal>
+    as a target in the <literal>UPDATE</literal> list, because syntactically
+    that must be a column of <literal>users</literal>.  We could write the same
+    function without depending on the <literal>variable_conflict</literal> setting
+    in this way:
+<programlisting>
+CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
+    &lt;&lt;fn&gt;&gt;
+    DECLARE
+        curtime timestamp := now();
+    BEGIN
+        UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
+          WHERE users.id = stamp_user.id;
+    END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+   </para>
+
+   <para>
+    Variable substitution does not happen in a command string given
+    to <command>EXECUTE</command> or one of its variants.  If you need to
+    insert a varying value into such a command, do so as part of
+    constructing the string value, or use <literal>USING</literal>, as illustrated in
+    <xref linkend="plpgsql-statements-executing-dyn"/>.
+   </para>
+
+   <para>
+    Variable substitution currently works only in <command>SELECT</command>,
+    <command>INSERT</command>, <command>UPDATE</command>,
+    <command>DELETE</command>, and commands containing one of
+    these (such as <command>EXPLAIN</command> and <command>CREATE TABLE
+    ... AS SELECT</command>),
+    because the main SQL engine allows query parameters only in these
+    commands.  To use a non-constant name or value in other statement
+    types (generically called utility statements), you must construct
+    the utility statement as a string and <command>EXECUTE</command> it.
+   </para>
+
+  </sect2>
+
+  <sect2 id="plpgsql-plan-caching">
+   <title>Plan Caching</title>
+
+   <para>
+    The <application>PL/pgSQL</application> interpreter 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</application> statement structure, but individual
+    <acronym>SQL</acronym> expressions and <acronym>SQL</acronym> commands
+    used in the function are not translated immediately.
+   </para>
+
+   <para>
+    <indexterm>
+     <primary>preparing a query</primary>
+     <secondary>in PL/pgSQL</secondary>
+    </indexterm>
+    As each expression and <acronym>SQL</acronym> command is first
+    executed in the function, the <application>PL/pgSQL</application> interpreter
+    parses and analyzes the command to create a prepared statement,
+    using the <acronym>SPI</acronym> manager's
+    <function>SPI_prepare</function> function.
+    Subsequent visits to that expression or command
+    reuse the prepared statement.  Thus, a function with conditional code
+    paths that are seldom visited will never incur the overhead of
+    analyzing those commands that are never executed within the current
+    session.  A disadvantage is that errors
+    in a specific expression or command cannot be detected until that
+    part of the function is reached in execution.  (Trivial syntax
+    errors will be detected during the initial parsing pass, but
+    anything deeper will not be detected until execution.)
+   </para>
+
+   <para>
+    <application>PL/pgSQL</application> (or more precisely, the SPI manager) can
+    furthermore attempt to cache the execution plan associated with any
+    particular prepared statement.  If a cached plan is not used, then
+    a fresh execution plan is generated on each visit to the statement,
+    and the current parameter values (that is, <application>PL/pgSQL</application>
+    variable values) can be used to optimize the selected plan.  If the
+    statement has no parameters, or is executed many times, the SPI manager
+    will consider creating a <firstterm>generic</firstterm> plan that is not dependent
+    on specific parameter values, and caching that for re-use.  Typically
+    this will happen only if the execution plan is not very sensitive to
+    the values of the <application>PL/pgSQL</application> variables referenced in it.
+    If it is, generating a plan each time is a net win.  See <xref
+    linkend="sql-prepare"/> for more information about the behavior of
+    prepared statements.
+   </para>
+
+   <para>
+    Because <application>PL/pgSQL</application> saves prepared statements
+    and sometimes 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 performing new parse analysis and
+    constructing a new execution plan on every execution.
+   </para>
+
+    <para>
+     The mutable nature of record variables presents another 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 from one call of the function to the next, since each
+     expression will be analyzed using the data type that is present
+     when the expression is first reached.  <command>EXECUTE</command> can be
+     used to get around this problem when necessary.
+    </para>
+
+    <para>
+     If the same function is used as a trigger for more than one table,
+     <application>PL/pgSQL</application> prepares and caches statements
+     independently for each such table &mdash; that is, there is a cache
+     for each trigger function and table combination, not just for each
+     function.  This alleviates some of the problems with varying
+     data types; for instance, a trigger function will be able to work
+     successfully with a column named <literal>key</literal> even if it happens
+     to have different types in different tables.
+    </para>
+
+    <para>
+     Likewise, functions having polymorphic argument types have a separate
+     statement cache for each combination of actual argument types they have
+     been invoked for, so that data type differences do not cause unexpected
+     failures.
+    </para>
+
+   <para>
+    Statement caching can sometimes have surprising effects on the
+    interpretation of time-sensitive values.  For example there
+    is a difference between what these two functions do:
+
+<programlisting>
+CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
+    BEGIN
+        INSERT INTO logtable VALUES (logtxt, 'now');
+    END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+     and:
+
+<programlisting>
+CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
+    DECLARE
+        curtime timestamp;
+    BEGIN
+        curtime := 'now';
+        INSERT INTO logtable VALUES (logtxt, curtime);
+    END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+    </para>
+
+    <para>
+     In the case of <function>logfunc1</function>, the
+     <productname>PostgreSQL</productname> main parser knows when
+     analyzing 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 <type>timestamp</type>
+     constant when the
+     <command>INSERT</command> is analyzed, 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.  A better idea is to use the <literal>now()</literal> or
+     <literal>current_timestamp</literal> function.
+    </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>textout</function> and <function>timestamp_in</function>
+     functions for the conversion.  So, the computed time stamp is updated
+     on each execution as the programmer expects.  Even though this
+     happens to work as expected, it's not terribly efficient, so
+     use of the <literal>now()</literal> function would still be a better idea.
+    </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</application> 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</command>. 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</application> is with a
+    GUI database access tool that facilitates development in a
+    procedural language. One example of such a tool is
+    <application>pgAdmin</application>, 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</application> 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 (assuming
+    escape string syntax is used).
+    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</quote> 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>$$</literal> to delimit fragments of SQL commands
+    that you are assembling as strings.  If you need to quote text that
+    includes <literal>$$</literal>, you could use <literal>$Q$</literal>, and so on.
+   </para>
+
+   <para>
+    The following chart shows what you have to do when writing quote
+    marks without dollar quoting.  It might be useful when translating
+    pre-dollar quoting code into something more comprehensible.
+  </para>
+
+  <variablelist>
+   <varlistentry id="plpgsql-quote-tips-1-quot">
+    <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</emphasis> appear in pairs.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry id="plpgsql-quote-tips-2-quot">
+    <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</application> parser would see
+      in either case.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry id="plpgsql-quote-tips-4-quot">
+    <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>
+      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>$$</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry id="plpgsql-quote-tips-6-quot">
+    <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 id="plpgsql-quote-tips-10-quot">
+    <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>
+
+  </sect2>
+  <sect2 id="plpgsql-extra-checks">
+   <title>Additional Compile-Time and Run-Time Checks</title>
+
+   <para>
+    To aid the user in finding instances of simple but common problems before
+    they cause harm, <application>PL/pgSQL</application> provides additional
+    <replaceable>checks</replaceable>. When enabled, depending on the configuration, they
+    can be used to emit either a <literal>WARNING</literal> or an <literal>ERROR</literal>
+    during the compilation of a function. A function which has received
+    a <literal>WARNING</literal> can be executed without producing further messages,
+    so you are advised to test in a separate development environment.
+   </para>
+
+   <para>
+    Setting <varname>plpgsql.extra_warnings</varname>, or
+    <varname>plpgsql.extra_errors</varname>, as appropriate, to <literal>"all"</literal>
+    is encouraged in development and/or testing environments.
+   </para>
+
+   <para>
+    These additional checks are enabled through the configuration variables
+    <varname>plpgsql.extra_warnings</varname> for warnings and
+    <varname>plpgsql.extra_errors</varname> for errors. Both can be set either to
+    a comma-separated list of checks, <literal>"none"</literal> or
+    <literal>"all"</literal>. The default is <literal>"none"</literal>. Currently
+    the list of available checks includes:
+    <variablelist>
+     <varlistentry id="plpgsql-extra-checks-shadowed-variables">
+      <term><varname>shadowed_variables</varname></term>
+      <listitem>
+       <para>
+        Checks if a declaration shadows a previously defined variable.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="plpgsql-extra-checks-strict-multi-assignment">
+      <term><varname>strict_multi_assignment</varname></term>
+      <listitem>
+       <para>
+        Some <application>PL/pgSQL</application> commands allow assigning
+        values to more than one variable at a time, such as
+        <command>SELECT INTO</command>.  Typically, the number of target
+        variables and the number of source variables should match, though
+        <application>PL/pgSQL</application> will use <literal>NULL</literal>
+        for missing values and extra variables are ignored.  Enabling this
+        check will cause <application>PL/pgSQL</application> to throw a
+        <literal>WARNING</literal> or <literal>ERROR</literal> whenever the
+        number of target variables and the number of source variables are
+        different.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="plpgsql-extra-checks-too-many-rows">
+      <term><varname>too_many_rows</varname></term>
+      <listitem>
+       <para>
+        Enabling this check will cause <application>PL/pgSQL</application> to
+        check if a given query returns more than one row when an
+        <literal>INTO</literal> clause is used.  As an <literal>INTO</literal>
+        statement will only ever use one row, having a query return multiple
+        rows is generally either inefficient and/or nondeterministic and
+        therefore is likely an error.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+
+    The following example shows the effect of <varname>plpgsql.extra_warnings</varname>
+    set to <varname>shadowed_variables</varname>:
+<programlisting>
+SET plpgsql.extra_warnings TO 'shadowed_variables';
+
+CREATE FUNCTION foo(f1 int) RETURNS int AS $$
+DECLARE
+f1 int;
+BEGIN
+RETURN f1;
+END;
+$$ LANGUAGE plpgsql;
+WARNING:  variable "f1" shadows a previously defined variable
+LINE 3: f1 int;
+        ^
+CREATE FUNCTION
+</programlisting>
+    The below example shows the effects of setting
+    <varname>plpgsql.extra_warnings</varname> to
+    <varname>strict_multi_assignment</varname>:
+<programlisting>
+SET plpgsql.extra_warnings TO 'strict_multi_assignment';
+
+CREATE OR REPLACE FUNCTION public.foo()
+ RETURNS void
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+  x int;
+  y int;
+BEGIN
+  SELECT 1 INTO x, y;
+  SELECT 1, 2 INTO x, y;
+  SELECT 1, 2, 3 INTO x, y;
+END;
+$$;
+
+SELECT foo();
+WARNING:  number of source and target fields in assignment does not match
+DETAIL:  strict_multi_assignment check of extra_warnings is active.
+HINT:  Make sure the query returns the exact list of columns.
+WARNING:  number of source and target fields in assignment does not match
+DETAIL:  strict_multi_assignment check of extra_warnings is active.
+HINT:  Make sure the query returns the exact list of columns.
+
+ foo
+-----
+
+(1 row)
+</programlisting>
+   </para>
+  </sect2>
+ </sect1>
+
+  <!-- **** Porting from Oracle PL/SQL **** -->
+
+ <sect1 id="plpgsql-porting">
+  <title>Porting from <productname>Oracle</productname> PL/SQL</title>
+
+  <indexterm zone="plpgsql-porting">
+   <primary>Oracle</primary>
+   <secondary>porting from PL/SQL to PL/pgSQL</secondary>
+  </indexterm>
+
+  <indexterm zone="plpgsql-porting">
+   <primary>PL/SQL (Oracle)</primary>
+   <secondary>porting to PL/pgSQL</secondary>
+  </indexterm>
+
+  <para>
+   This section explains differences between
+   <productname>PostgreSQL</productname>'s <application>PL/pgSQL</application>
+   language and Oracle's <application>PL/SQL</application> language,
+   to help developers who port applications from
+   <trademark class="registered">Oracle</trademark> to <productname>PostgreSQL</productname>.
+  </para>
+
+  <para>
+   <application>PL/pgSQL</application> is similar to PL/SQL in many
+   aspects. It is a block-structured, imperative language, and all
+   variables have to be declared.  Assignments, loops, and conditionals
+   are similar.  The main differences you should keep in mind when
+   porting from <application>PL/SQL</application> to
+   <application>PL/pgSQL</application> are:
+
+    <itemizedlist>
+     <listitem>
+      <para>
+       If a name used in an SQL command could be either a column name of a
+       table used in the command or a reference to a variable of the function,
+       <application>PL/SQL</application> treats it as a column name.
+       By default, <application>PL/pgSQL</application> will throw an error
+       complaining that the name is ambiguous.  You can specify
+       <literal>plpgsql.variable_conflict</literal> = <literal>use_column</literal>
+       to change this behavior to match <application>PL/SQL</application>,
+       as explained in <xref linkend="plpgsql-var-subst"/>.
+       It's often best to avoid such ambiguities in the first place,
+       but if you have to port a large amount of code that depends on
+       this behavior, setting <literal>variable_conflict</literal> may be the
+       best solution.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       In <productname>PostgreSQL</productname> the function body must be written as
+       a string literal.  Therefore you need to use dollar quoting or escape
+       single quotes in the function body. (See <xref
+       linkend="plpgsql-quote-tips"/>.)
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       Data type names often need translation.  For example, in Oracle string
+       values are commonly declared as being of type <type>varchar2</type>, which
+       is a non-SQL-standard type.  In <productname>PostgreSQL</productname>,
+       use type <type>varchar</type> or <type>text</type> instead.  Similarly, replace
+       type <type>number</type> with <type>numeric</type>, or use some other numeric
+       data type if there's a more appropriate one.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       Instead of packages, use schemas to organize your functions
+       into groups.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       Since there are no packages, there are no package-level variables
+       either. This is somewhat annoying.  You can keep per-session state
+       in temporary tables instead.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       Integer <command>FOR</command> loops with <literal>REVERSE</literal> work
+       differently: <application>PL/SQL</application> counts down from the second
+       number to the first, while <application>PL/pgSQL</application> counts down
+       from the first number to the second, requiring the loop bounds
+       to be swapped when porting.  This incompatibility is unfortunate
+       but is unlikely to be changed. (See <xref
+       linkend="plpgsql-integer-for"/>.)
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       <command>FOR</command> loops over queries (other than cursors) also work
+       differently: the target variable(s) must have been declared,
+       whereas <application>PL/SQL</application> always declares them implicitly.
+       An advantage of this is that the variable values are still accessible
+       after the loop exits.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       There are various notational differences for the use of cursor
+       variables.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+  <sect2 id="plpgsql-porting-examples">
+   <title>Porting Examples</title>
+
+   <para>
+    <xref linkend="pgsql-porting-ex1"/> shows how to port a simple
+    function from <application>PL/SQL</application> to <application>PL/pgSQL</application>.
+   </para>
+
+   <example id="pgsql-porting-ex1">
+    <title>Porting a Simple Function from <application>PL/SQL</application> to <application>PL/pgSQL</application></title>
+
+    <para>
+     Here is an <productname>Oracle</productname> <application>PL/SQL</application> function:
+<programlisting>
+CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
+                                                  v_version varchar2)
+RETURN varchar2 IS
+BEGIN
+    IF v_version IS NULL THEN
+        RETURN v_name;
+    END IF;
+    RETURN v_name || '/' || v_version;
+END;
+/
+show errors;
+</programlisting>
+    </para>
+
+    <para>
+     Let's go through this function and see the differences compared to
+     <application>PL/pgSQL</application>:
+
+     <itemizedlist>
+      <listitem>
+       <para>
+        The type name <type>varchar2</type> has to be changed to <type>varchar</type>
+        or <type>text</type>.  In the examples in this section, we'll
+        use <type>varchar</type>, but <type>text</type> is often a better choice if
+        you do not need specific string length limits.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        The <literal>RETURN</literal> key word in the function
+        prototype (not the function body) becomes
+        <literal>RETURNS</literal> in
+        <productname>PostgreSQL</productname>.
+        Also, <literal>IS</literal> becomes <literal>AS</literal>, and you need to
+        add a <literal>LANGUAGE</literal> clause because <application>PL/pgSQL</application>
+        is not the only possible function language.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        In <productname>PostgreSQL</productname>, the function body is considered
+        to be a string literal, so you need to use quote marks or dollar
+        quotes around it.  This substitutes for the terminating <literal>/</literal>
+        in the Oracle approach.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        The <literal>show errors</literal> command does not exist in
+        <productname>PostgreSQL</productname>, and is not needed since errors are
+        reported automatically.
+       </para>
+      </listitem>
+     </itemizedlist>
+    </para>
+
+    <para>
+     This is how this function would look when ported to
+     <productname>PostgreSQL</productname>:
+
+<programlisting>
+CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
+                                                  v_version varchar)
+RETURNS varchar AS $$
+BEGIN
+    IF v_version IS NULL THEN
+        RETURN v_name;
+    END IF;
+    RETURN v_name || '/' || v_version;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+    </para>
+   </example>
+
+   <para>
+    <xref linkend="plpgsql-porting-ex2"/> shows how to port a
+    function that creates another function and how to handle the
+    ensuing quoting problems.
+   </para>
+
+   <example id="plpgsql-porting-ex2">
+    <title>Porting a Function that Creates Another Function from <application>PL/SQL</application> to <application>PL/pgSQL</application></title>
+
+    <para>
+     The following procedure grabs rows from a
+     <command>SELECT</command> statement and builds a large function
+     with the results in <literal>IF</literal> statements, for the
+     sake of efficiency.
+    </para>
+
+    <para>
+     This is the Oracle version:
+<programlisting>
+CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
+    CURSOR referrer_keys IS
+        SELECT * FROM cs_referrer_keys
+        ORDER BY try_order;
+    func_cmd VARCHAR(4000);
+BEGIN
+    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
+                 v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';
+
+    FOR referrer_key IN referrer_keys LOOP
+        func_cmd := func_cmd ||
+          ' IF v_' || referrer_key.kind
+          || ' LIKE ''' || referrer_key.key_string
+          || ''' THEN RETURN ''' || referrer_key.referrer_type
+          || '''; END IF;';
+    END LOOP;
+
+    func_cmd := func_cmd || ' RETURN NULL; END;';
+
+    EXECUTE IMMEDIATE func_cmd;
+END;
+/
+show errors;
+</programlisting>
+    </para>
+
+    <para>
+     Here is how this function would end up in <productname>PostgreSQL</productname>:
+<programlisting>
+CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
+DECLARE
+    referrer_keys CURSOR IS
+        SELECT * FROM cs_referrer_keys
+        ORDER BY try_order;
+    func_body text;
+    func_cmd text;
+BEGIN
+    func_body := 'BEGIN';
+
+    FOR referrer_key IN referrer_keys LOOP
+        func_body := func_body ||
+          ' IF v_' || referrer_key.kind
+          || ' LIKE ' || quote_literal(referrer_key.key_string)
+          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
+          || '; END IF;' ;
+    END LOOP;
+
+    func_body := func_body || ' RETURN NULL; END;';
+
+    func_cmd :=
+      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
+                                                        v_domain varchar,
+                                                        v_url varchar)
+        RETURNS varchar AS '
+      || quote_literal(func_body)
+      || ' LANGUAGE plpgsql;' ;
+
+    EXECUTE func_cmd;
+END;
+$func$ LANGUAGE plpgsql;
+</programlisting>
+     Notice how the body of the function is built separately and passed
+     through <literal>quote_literal</literal> to double any quote marks in it.  This
+     technique is needed because we cannot safely use dollar quoting for
+     defining the new function: we do not know for sure what strings will
+     be interpolated from the <structfield>referrer_key.key_string</structfield> field.
+     (We are assuming here that <structfield>referrer_key.kind</structfield> can be
+     trusted to always be <literal>host</literal>, <literal>domain</literal>, or
+     <literal>url</literal>, but <structfield>referrer_key.key_string</structfield> might be
+     anything, in particular it might contain dollar signs.) This function
+     is actually an improvement on the Oracle original, because it will
+     not generate broken code when <structfield>referrer_key.key_string</structfield> or
+     <structfield>referrer_key.referrer_type</structfield> contain quote marks.
+    </para>
+   </example>
+
+   <para>
+    <xref linkend="plpgsql-porting-ex3"/> shows how to port a function
+    with <literal>OUT</literal> parameters and string manipulation.
+    <productname>PostgreSQL</productname> does not have a built-in
+    <function>instr</function> function, but you can create one
+    using a combination of other
+    functions. In <xref linkend="plpgsql-porting-appendix"/> there is a
+    <application>PL/pgSQL</application> implementation of
+    <function>instr</function> that you can use to make your porting
+    easier.
+   </para>
+
+   <example id="plpgsql-porting-ex3">
+    <title>Porting a Procedure With String Manipulation and
+    <literal>OUT</literal> Parameters from <application>PL/SQL</application> to
+    <application>PL/pgSQL</application></title>
+
+    <para>
+     The following <productname>Oracle</productname> PL/SQL procedure is used
+     to parse a URL and return several elements (host, path, and query).
+    </para>
+
+    <para>
+     This is the Oracle version:
+<programlisting>
+CREATE OR REPLACE PROCEDURE cs_parse_url(
+    v_url IN VARCHAR2,
+    v_host OUT VARCHAR2,  -- This will be passed back
+    v_path OUT VARCHAR2,  -- This one too
+    v_query OUT VARCHAR2) -- And this one
+IS
+    a_pos1 INTEGER;
+    a_pos2 INTEGER;
+BEGIN
+    v_host := NULL;
+    v_path := NULL;
+    v_query := NULL;
+    a_pos1 := instr(v_url, '//');
+
+    IF a_pos1 = 0 THEN
+        RETURN;
+    END IF;
+    a_pos2 := instr(v_url, '/', a_pos1 + 2);
+    IF a_pos2 = 0 THEN
+        v_host := substr(v_url, a_pos1 + 2);
+        v_path := '/';
+        RETURN;
+    END IF;
+
+    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
+    a_pos1 := instr(v_url, '?', a_pos2 + 1);
+
+    IF a_pos1 = 0 THEN
+        v_path := substr(v_url, a_pos2);
+        RETURN;
+    END IF;
+
+    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
+    v_query := substr(v_url, a_pos1 + 1);
+END;
+/
+show errors;
+</programlisting>
+    </para>
+
+    <para>
+     Here is a possible translation into <application>PL/pgSQL</application>:
+<programlisting>
+CREATE OR REPLACE FUNCTION cs_parse_url(
+    v_url IN VARCHAR,
+    v_host OUT VARCHAR,  -- This will be passed back
+    v_path OUT VARCHAR,  -- This one too
+    v_query OUT VARCHAR) -- And this one
+AS $$
+DECLARE
+    a_pos1 INTEGER;
+    a_pos2 INTEGER;
+BEGIN
+    v_host := NULL;
+    v_path := NULL;
+    v_query := NULL;
+    a_pos1 := instr(v_url, '//');
+
+    IF a_pos1 = 0 THEN
+        RETURN;
+    END IF;
+    a_pos2 := instr(v_url, '/', a_pos1 + 2);
+    IF a_pos2 = 0 THEN
+        v_host := substr(v_url, a_pos1 + 2);
+        v_path := '/';
+        RETURN;
+    END IF;
+
+    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
+    a_pos1 := instr(v_url, '?', a_pos2 + 1);
+
+    IF a_pos1 = 0 THEN
+        v_path := substr(v_url, a_pos2);
+        RETURN;
+    END IF;
+
+    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
+    v_query := substr(v_url, a_pos1 + 1);
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+     This function could be used like this:
+<programlisting>
+SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
+</programlisting>
+    </para>
+   </example>
+
+   <para>
+    <xref linkend="plpgsql-porting-ex4"/> shows how to port a procedure
+    that uses numerous features that are specific to Oracle.
+   </para>
+
+   <example id="plpgsql-porting-ex4">
+    <title>Porting a Procedure from <application>PL/SQL</application> to <application>PL/pgSQL</application></title>
+
+    <para>
+     The Oracle version:
+
+<programlisting>
+CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
+    a_running_job_count INTEGER;
+BEGIN
+    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
+
+    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
+
+    IF a_running_job_count &gt; 0 THEN
+        COMMIT; -- free lock
+        raise_application_error(-20000,
+                 'Unable to create a new job: a job is currently running.');
+    END IF;
+
+    DELETE FROM cs_active_job;
+    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
+
+    BEGIN
+        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
+    EXCEPTION
+        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
+    END;
+    COMMIT;
+END;
+/
+show errors
+</programlisting>
+   </para>
+
+   <para>
+    This is how we could port this procedure to <application>PL/pgSQL</application>:
+
+<programlisting>
+CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
+DECLARE
+    a_running_job_count integer;
+BEGIN
+    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
+
+    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
+
+    IF a_running_job_count &gt; 0 THEN
+        COMMIT; -- free lock
+        RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- <co id="co.plpgsql-porting-raise"/>
+    END IF;
+
+    DELETE FROM cs_active_job;
+    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
+
+    BEGIN
+        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
+    EXCEPTION
+        WHEN unique_violation THEN -- <co id="co.plpgsql-porting-exception"/>
+            -- don't worry if it already exists
+    END;
+    COMMIT;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+    <calloutlist>
+     <callout arearefs="co.plpgsql-porting-raise">
+      <para>
+       The syntax of <literal>RAISE</literal> is considerably different from
+       Oracle's statement, although the basic case <literal>RAISE</literal>
+       <replaceable class="parameter">exception_name</replaceable> works
+       similarly.
+      </para>
+     </callout>
+     <callout arearefs="co.plpgsql-porting-exception">
+      <para>
+       The exception names supported by <application>PL/pgSQL</application> are
+       different from Oracle's.  The set of built-in exception names
+       is much larger (see <xref linkend="errcodes-appendix"/>).  There
+       is not currently a way to declare user-defined exception names,
+       although you can throw user-chosen SQLSTATE values instead.
+      </para>
+     </callout>
+    </calloutlist>
+   </para>
+   </example>
+  </sect2>
+
+  <sect2 id="plpgsql-porting-other">
+   <title>Other Things to Watch For</title>
+
+   <para>
+    This section explains a few other things to watch for when porting
+    Oracle <application>PL/SQL</application> functions to
+    <productname>PostgreSQL</productname>.
+   </para>
+
+   <sect3 id="plpgsql-porting-exceptions">
+    <title>Implicit Rollback after Exceptions</title>
+
+    <para>
+     In <application>PL/pgSQL</application>, when an exception is caught by an
+     <literal>EXCEPTION</literal> clause, all database changes since the block's
+     <literal>BEGIN</literal> are automatically rolled back.  That is, the behavior
+     is equivalent to what you'd get in Oracle with:
+
+<programlisting>
+BEGIN
+    SAVEPOINT s1;
+    ... code here ...
+EXCEPTION
+    WHEN ... THEN
+        ROLLBACK TO s1;
+        ... code here ...
+    WHEN ... THEN
+        ROLLBACK TO s1;
+        ... code here ...
+END;
+</programlisting>
+
+     If you are translating an Oracle procedure that uses
+     <command>SAVEPOINT</command> and <command>ROLLBACK TO</command> in this style,
+     your task is easy: just omit the <command>SAVEPOINT</command> and
+     <command>ROLLBACK TO</command>.  If you have a procedure that uses
+     <command>SAVEPOINT</command> and <command>ROLLBACK TO</command> in a different way
+     then some actual thought will be required.
+    </para>
+   </sect3>
+
+   <sect3 id="plpgsql-porting-other-execute">
+    <title><command>EXECUTE</command></title>
+
+    <para>
+     The <application>PL/pgSQL</application> version of
+     <command>EXECUTE</command> works similarly to the
+     <application>PL/SQL</application> version, but you have to remember to use
+     <function>quote_literal</function> and
+     <function>quote_ident</function> as described in <xref
+     linkend="plpgsql-statements-executing-dyn"/>.  Constructs of the
+     type <literal>EXECUTE 'SELECT * FROM $1';</literal> will not work
+     reliably unless you use these functions.
+    </para>
+   </sect3>
+
+   <sect3 id="plpgsql-porting-optimization">
+    <title>Optimizing <application>PL/pgSQL</application> Functions</title>
+
+    <para>
+     <productname>PostgreSQL</productname> gives you two function creation
+     modifiers to optimize execution: <quote>volatility</quote> (whether
+     the function always returns the same result when given the same
+     arguments) and <quote>strictness</quote> (whether the function
+     returns null if any argument is null).  Consult the <xref
+     linkend="sql-createfunction"/>
+     reference page for details.
+    </para>
+
+    <para>
+     When making use of these optimization attributes, your
+     <command>CREATE FUNCTION</command> statement might look something
+     like this:
+
+<programlisting>
+CREATE FUNCTION foo(...) RETURNS integer AS $$
+...
+$$ LANGUAGE plpgsql STRICT IMMUTABLE;
+</programlisting>
+    </para>
+   </sect3>
+  </sect2>
+
+  <sect2 id="plpgsql-porting-appendix">
+   <title>Appendix</title>
+
+   <para>
+    This section contains the code for a set of Oracle-compatible
+    <function>instr</function> functions that you can use to simplify
+    your porting efforts.
+   </para>
+
+   <indexterm>
+    <primary><function>instr</function> function</primary>
+   </indexterm>
+
+<programlisting><![CDATA[
+--
+-- instr functions that mimic Oracle's counterpart
+-- Syntax: instr(string1, string2 [, n [, m]])
+-- where [] denotes optional parameters.
+--
+-- Search string1, beginning at the nth character, for the mth occurrence
+-- of string2.  If n is negative, search backwards, starting at the abs(n)'th
+-- character from the end of string1.
+-- If n is not passed, assume 1 (search starts at first character).
+-- If m is not passed, assume 1 (find first occurrence).
+-- Returns starting index of string2 in string1, or 0 if string2 is not found.
+--
+
+CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
+BEGIN
+    RETURN instr($1, $2, 1);
+END;
+$$ LANGUAGE plpgsql STRICT IMMUTABLE;
+
+
+CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
+                      beg_index integer)
+RETURNS integer AS $$
+DECLARE
+    pos integer NOT NULL DEFAULT 0;
+    temp_str varchar;
+    beg integer;
+    length integer;
+    ss_length integer;
+BEGIN
+    IF beg_index > 0 THEN
+        temp_str := substring(string FROM beg_index);
+        pos := position(string_to_search_for IN temp_str);
+
+        IF pos = 0 THEN
+            RETURN 0;
+        ELSE
+            RETURN pos + beg_index - 1;
+        END IF;
+    ELSIF beg_index < 0 THEN
+        ss_length := char_length(string_to_search_for);
+        length := char_length(string);
+        beg := length + 1 + beg_index;
+
+        WHILE beg > 0 LOOP
+            temp_str := substring(string FROM beg FOR ss_length);
+            IF string_to_search_for = temp_str THEN
+                RETURN beg;
+            END IF;
+
+            beg := beg - 1;
+        END LOOP;
+
+        RETURN 0;
+    ELSE
+        RETURN 0;
+    END IF;
+END;
+$$ LANGUAGE plpgsql STRICT IMMUTABLE;
+
+
+CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
+                      beg_index integer, occur_index integer)
+RETURNS integer AS $$
+DECLARE
+    pos integer NOT NULL DEFAULT 0;
+    occur_number integer NOT NULL DEFAULT 0;
+    temp_str varchar;
+    beg integer;
+    i integer;
+    length integer;
+    ss_length integer;
+BEGIN
+    IF occur_index <= 0 THEN
+        RAISE 'argument ''%'' is out of range', occur_index
+          USING ERRCODE = '22003';
+    END IF;
+
+    IF beg_index > 0 THEN
+        beg := beg_index - 1;
+        FOR i IN 1..occur_index LOOP
+            temp_str := substring(string FROM beg + 1);
+            pos := position(string_to_search_for IN temp_str);
+            IF pos = 0 THEN
+                RETURN 0;
+            END IF;
+            beg := beg + pos;
+        END LOOP;
+
+        RETURN beg;
+    ELSIF beg_index < 0 THEN
+        ss_length := char_length(string_to_search_for);
+        length := char_length(string);
+        beg := length + 1 + beg_index;
+
+        WHILE beg > 0 LOOP
+            temp_str := substring(string FROM beg FOR ss_length);
+            IF string_to_search_for = temp_str THEN
+                occur_number := occur_number + 1;
+                IF occur_number = occur_index THEN
+                    RETURN beg;
+                END IF;
+            END IF;
+
+            beg := beg - 1;
+        END LOOP;
+
+        RETURN 0;
+    ELSE
+        RETURN 0;
+    END IF;
+END;
+$$ LANGUAGE plpgsql STRICT IMMUTABLE;
+]]>
+</programlisting>
+  </sect2>
+
+ </sect1>
+
+</chapter>