From c892643a3c4645450e3f84d2e2266a4e46340d28 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 16 Jun 2006 23:29:27 +0000
Subject: Code review for SELECT INTO STRICT patch: use saner choices of error
 SQLSTATEs, fix some documentation problems.

---
 doc/src/sgml/errcodes.sgml | 14 +++++++++++++-
 doc/src/sgml/plpgsql.sgml  | 43 +++++++++++++++++++++++++------------------
 2 files changed, 38 insertions(+), 19 deletions(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/errcodes.sgml b/doc/src/sgml/errcodes.sgml
index b5617c98507..bf06cfaf5e5 100644
--- a/doc/src/sgml/errcodes.sgml
+++ b/doc/src/sgml/errcodes.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.19 2006/06/16 22:41:45 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.20 2006/06/16 23:29:26 tgl Exp $ -->
 
 <appendix id="errcodes-appendix">
  <title><productname>PostgreSQL</productname> Error Codes</title>
@@ -1344,6 +1344,18 @@
 <entry>raise_exception</entry>
 </row>
 
+<row>
+<entry><literal>P0002</literal></entry>
+<entry>NO DATA FOUND</entry>
+<entry>no_data_found</entry>
+</row>
+
+<row>
+<entry><literal>P0003</literal></entry>
+<entry>TOO MANY ROWS</entry>
+<entry>too_many_rows</entry>
+</row>
+
 
 <row>
 <entry spanname="span13"><emphasis role="bold">Class XX &mdash; Internal Error</></entry>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 1f09b9e99c7..ac5b2b4cfc6 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.96 2006/06/15 18:02:22 momjian Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.97 2006/06/16 23:29:26 tgl Exp $ -->
 
 <chapter id="plpgsql"> 
   <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1076,8 +1076,8 @@ tax := subtotal * 0.06;
     </indexterm>
 
     <para>
-     The result of a <command>SELECT</command> command yielding multiple columns (but
-     only one row) can be assigned to a record variable, row-type
+     The result of a <command>SELECT</command> command yielding multiple
+     columns (but only one row) can be assigned to a record variable, row-type
      variable, or list of scalar variables.  This is done by:
 
 <synopsis>
@@ -1126,23 +1126,24 @@ SELECT INTO <optional>STRICT</optional> <replaceable>target</replaceable> <repla
      <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:
+     well-defined unless you've used <literal>ORDER BY</>.)  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 STRICT myrec * FROM emp WHERE empname = myname;
+SELECT INTO myrec * FROM emp WHERE empname = myname;
 IF NOT FOUND THEN
     RAISE EXCEPTION 'employee % not found', myname;
 END IF;
 </programlisting>
 
-    <para>
-     If the <literal>STRICT</literal> option is specified, a query must
+     If the <literal>STRICT</literal> option is specified, the 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:
+     (more than one row). You can use an exception block if you wish
+     to catch the error, for example:
 
 <programlisting>
 BEGIN;
@@ -1154,11 +1155,17 @@ BEGIN;
             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.
+     Successful execution of <command>SELECT INTO STRICT</command>
+     always sets <literal>FOUND</literal> to true.
     </para>
 
+    <note>
+     <para>
+      <command>SELECT INTO STRICT</command> matches the behavior of
+      Oracle PL/SQL's <command>SELECT INTO</command> statement.
+     </para>
+    </note>
+
    </sect2>
 
    <sect2 id="plpgsql-statements-perform">
@@ -1987,7 +1994,7 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
         the loop. If the <literal>BY</> clause isn't specified the iteration 
         step is 1 otherwise it's the value specified in the <literal>BY</> 
         clause. If <literal>REVERSE</> is specified then the step value is 
-		considered negative.
+        considered negative.
        </para>
 
        <para>
@@ -2764,7 +2771,7 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
       <para>
        Data type <type>name</type>; the name of the table that caused the trigger
        invocation. This is now deprecated, and could disappear in a future 
-	   release. Use <literal>TG_TABLE_NAME</> instead.
+       release. Use <literal>TG_TABLE_NAME</> instead.
       </para>
      </listitem>
     </varlistentry>
@@ -2774,7 +2781,7 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
      <listitem>
       <para>
        Data type <type>name</type>; the name of the table that 
-	   caused the trigger invocation.
+       caused the trigger invocation.
       </para>
      </listitem>
     </varlistentry>
@@ -2784,7 +2791,7 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
      <listitem>
       <para>
        Data type <type>name</type>; the name of the schema of the 
-	   table that caused the trigger invocation.
+       table that caused the trigger invocation.
       </para>
      </listitem>
     </varlistentry>
-- 
cgit v1.2.3