Add STRICT to PL/pgSQL SELECT INTO, so exceptions are thrown if more or

less than one row is returned by the SELECT, for Oracle PL/SQL
compatibility.

Improve SELECT INTO documentation.

Matt Miller

1 files changed, 29 insertions, 33 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 60c7593362b..1f09b9e99c7 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.95 2006/06/12 16:45:30 momjian Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.96 2006/06/15 18:02:22 momjian Exp $ -->
 
 <chapter id="plpgsql"> 
   <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1081,7 +1081,7 @@ tax := subtotal * 0.06;
      variable, or list of scalar variables.  This is done by:
 
 <synopsis>
-SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
+SELECT INTO <optional>STRICT</optional> <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
 </synopsis>
 
      where <replaceable>target</replaceable> can be a record variable, a row
@@ -1122,47 +1122,43 @@ SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</r
     </para>
 
     <para>
-     If the query returns zero rows, null values are assigned to the
-     target(s).  If the query returns multiple rows, the first
-     row is assigned to the target(s) and the rest are discarded.
-     (Note that <quote>the first row</> is not well-defined unless you've
-     used <literal>ORDER BY</>.)
-    </para>
-
-    <para>
-     You can check the special <literal>FOUND</literal> variable (see
-     <xref linkend="plpgsql-statements-diagnostics">) after a
-     <command>SELECT INTO</command> statement to determine whether the
-     assignment was successful, that is, at least one row was was returned by
-     the query. For example:
+     If <literal>STRICT</literal> is not specified then
+     <replaceable>target</replaceable> will be set to the first row
+     returned by the query, or if the query returned no rows,
+     null values are assigned. (Note that <quote>the first row</> is not
+     well-defined unless you've used <literal>ORDER BY</>.)
+     You can check the special <literal>FOUND</literal> variable to
+     determine if any rows were found:
 
 <programlisting>
-SELECT INTO myrec * FROM emp WHERE empname = myname;
+SELECT INTO STRICT myrec * FROM emp WHERE empname = myname;
 IF NOT FOUND THEN
     RAISE EXCEPTION 'employee % not found', myname;
 END IF;
 </programlisting>
-    </para>
 
     <para>
-     To test for whether a record/row result is null, you can use the
-     <literal>IS NULL</literal> conditional.  There is, however, no
-     way to tell whether any additional rows might have been
-     discarded.  Here is an example that handles the case where no
-     rows have been returned:
-<programlisting>
-DECLARE
-    users_rec RECORD;
-BEGIN
-    SELECT INTO users_rec * FROM users WHERE user_id=3;
+     If the <literal>STRICT</literal> option is specified, a query must
+     return exactly one row or a run-time error will be thrown, either
+     <literal>NO_DATA_FOUND</> (no rows) or <literal>TOO_MANY_ROWS</>
+     (more than one row). You can must use exception blocks to determine
+     the number of rows generated by the query:
 
-    IF users_rec.homepage IS NULL THEN
-        -- user entered no homepage, return "http://"
-        RETURN 'http://';
-    END IF;
+<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>
+     Only <command>SELECT INTO STRICT</command> allows you to check if more
+     than one row was retrieved.  <command>SELECT INTO STRICT</command>
+     matches Oracle's PL/SQL <command>SELECT INTO</command> behavior.
     </para>
+
    </sect2>
 
    <sect2 id="plpgsql-statements-perform">
@@ -1424,8 +1420,8 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
          <itemizedlist>
           <listitem>
            <para>
-                A <command>SELECT INTO</command> statement sets
-                <literal>FOUND</literal> true if it returns a row, false if no
+                A <command>SELECT INTO</command> statement sets 
+                <literal>FOUND</literal> true if a row is assigned, false if no
                 row is returned.
            </para>
           </listitem>