select_into_exact.patch

Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /var/local/pgcvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.75
diff -c -r1.75 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml	2 Jul 2005 08:59:47 -0000	1.75
--- doc/src/sgml/plpgsql.sgml	29 Jul 2005 19:19:56 -0000
***************
*** 1067,1073 ****
       variable, or list of scalar variables.  This is done by:
  
  <synopsis>
! SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
  </synopsis>
  
       where <replaceable>target</replaceable> can be a record variable, a row
--- 1067,1073 ----
       variable, or list of scalar variables.  This is done by:
  
  <synopsis>
! SELECT INTO <optional>EXACT</optional> <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
  </synopsis>
  
       where <replaceable>target</replaceable> can be a record variable, a row
***************
*** 1108,1126 ****
      </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:
  
  <programlisting>
  SELECT INTO myrec * FROM emp WHERE empname = myname;
--- 1108,1130 ----
      </para>
  
      <para>
!      If the <literal>EXACT</literal> option is specified, then
!      <replaceable>target</replaceable> will not be set unless the query
!      returns exactly one row.  If <literal>EXACT</literal> is not
!      specified then <replaceable>target</replaceable> will be set
!      regardless of the number of rows returned by the query.  In the
!      non-<literal>EXACT</literal> case, null values are assigned if the
!      query returns zero rows, and the first row is assigned if the query
!      returns more than 1 row.  (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 after a
!      <command>SELECT INTO</command> to determine whether the statement was
!      successful.  A non-<literal>EXACT</literal> query is considered successful
!      if any rows are returned, and an <literal>EXACT</literal> query is
!      successful only if exactly 1 row is returned.  For example:
  
  <programlisting>
  SELECT INTO myrec * FROM emp WHERE empname = myname;
***************
*** 1128,1141 ****
      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;
--- 1132,1196 ----
      RAISE EXCEPTION 'employee % not found', myname;
  END IF;
  </programlisting>
+ 
+ <programlisting>
+ SELECT INTO EXACT myrec * FROM emp WHERE empname = myname;
+ IF NOT FOUND THEN
+     RAISE EXCEPTION 'employee % not found or not unique', myname;
+ END IF;
+ </programlisting>
+     </para>
+ 
+     <para>
+     When using the <literal>EXACT</literal> option you can distinguish the
+     not-found case from the not-unique case by using
+     <command>GET DIAGNOSTICS</command> (see
+     <xref linkend="plpgsql-statements-diagnostics">) to retrieve
+     <literal>ROW_COUNT</literal>.  After a
+     <command>SELECT INTO EXACT</command> statement
+     <literal>ROW_COUNT</literal> will not necessarily be equal to the total
+     number of rows matching the query, but will be equal to 0, 1, or 2,
+     indicating no matching rows, exactly one matching row, or greater than one
+     matching row, respectively.  For example:
+ 
+ <programlisting>
+ SELECT INTO EXACT myrec * FROM emp WHERE empname = myname;
+ IF NOT FOUND THEN
+     GET DIAGNOSTICS rowcount = ROW_COUNT;
+     IF rowcount == 0 THEN
+         RAISE EXCEPTION 'employee % not found', myname;
+     ELSE
+         RAISE EXCEPTION 'employee % not unique', myname;
+     END IF;
+ END IF;
+ </programlisting>
      </para>
  
      <para>
!     To test for whether a record/row result is null, you can use the
!     <literal>IS NULL</literal> conditional.  Keep in mind that a result
!     might be null for any of a number of reasons:
!         <itemizedlist>
!           <listitem>
!            <para>
!             The query could return a row with a null value.
!            </para>
!           </listitem>
!           <listitem>
!            <para>
!             A non-<literal>EXACT</literal> query could return no rows and set all targets
!             to null.
!            </para>
!           </listitem>
!           <listitem>
!            <para>
!             An <literal>EXACT</literal> query could return either zero or multiple rows,
!             leaving all targets untouched.  If a target was null to begin with it will be
!             null after the <command>SELECT INTO EXACT</command>
!            </para>
!           </listitem>
!          </itemizedlist>
!      Here is an <literal>IS NULL</literal> example:
  <programlisting>
  DECLARE
      users_rec RECORD;
***************
*** 1143,1149 ****
      SELECT INTO users_rec * FROM users WHERE user_id=3;
  
      IF users_rec.homepage IS NULL THEN
!         -- user entered no homepage, return "http://"
          RETURN 'http://';
      END IF;
  END;
--- 1198,1204 ----
      SELECT INTO users_rec * FROM users WHERE user_id=3;
  
      IF users_rec.homepage IS NULL THEN
!         -- user entered no homepage or user_id 3 not found, return "http://"
          RETURN 'http://';
      END IF;
  END;
***************
*** 1403,1411 ****
           <itemizedlist>
            <listitem>
             <para>
!                 A <command>SELECT INTO</command> statement sets
!                 <literal>FOUND</literal> true if it returns a row, false if no
!                 row is returned.
             </para>
            </listitem>
            <listitem>
--- 1458,1468 ----
           <itemizedlist>
            <listitem>
             <para>
!                 A <command>SELECT INTO</command> statement without the
!                 <literal>EXACT</literal> option sets <literal>FOUND</literal>
!                 true if it finds at least one row.  A
!                 <command>SELECT INTO EXACT</command> sets
!                 <literal>FOUND</literal> true if it finds exactly one row.
             </para>
            </listitem>
            <listitem>
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.80
diff -c -r1.80 gram.y
*** src/pl/plpgsql/src/gram.y	2 Jul 2005 17:01:59 -0000	1.80
--- src/pl/plpgsql/src/gram.y	29 Jul 2005 19:43:41 -0000
***************
*** 171,176 ****
--- 171,177 ----
  %token	K_ELSE
  %token	K_ELSIF
  %token	K_END
+ %token	K_EXACT
  %token	K_EXCEPTION
  %token	K_EXECUTE
  %token	K_EXIT
***************
*** 1886,1891 ****
--- 1887,1893 ----
  	PLpgSQL_rec			*rec = NULL;
  	int					tok;
  	bool				have_into = false;
+ 	bool				have_exact = false;
  
  	plpgsql_dstring_init(&ds);
  	plpgsql_dstring_append(&ds, "SELECT ");
***************
*** 1913,1918 ****
--- 1915,1925 ----
  						 errmsg("INTO specified more than once")));
  			}
  			tok = yylex();
+ 			if (tok == K_EXACT)
+ 			{
+ 				have_exact = true;
+ 				tok = yylex();
+ 			}
  			switch (tok)
  			{
  				case T_ROW:
***************
*** 1996,2001 ****
--- 2003,2011 ----
  		select->row		 = row;
  		select->query	 = expr;
  
+ 		if (have_exact)
+   		    select->exact = true;
+ 
  		return (PLpgSQL_stmt *)select;
  	}
  	else
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.151
diff -c -r1.151 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c	28 Jul 2005 07:51:13 -0000	1.151
--- src/pl/plpgsql/src/pl_exec.c	29 Jul 2005 19:19:56 -0000
***************
*** 1650,1670 ****
  
  	/*
  	 * Run the query
  	 */
! 	exec_run_select(estate, stmt->query, 1, NULL);
  	tuptab = estate->eval_tuptable;
  	n = estate->eval_processed;
  
  	/*
! 	 * If the query didn't return any rows, set the target to NULL and
! 	 * return.
  	 */
! 	if (n == 0)
  	{
- 		exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
  		exec_eval_cleanup(estate);
  		return PLPGSQL_RC_OK;
  	}
  
  	/*
  	 * Put the result into the target and set found to true
--- 1650,1683 ----
  
  	/*
  	 * Run the query
+ 	 * If SELECT ... INTO EXACT was specified, bring back up to 2 rows to
+ 	 * ensure query was exact
  	 */
! 	exec_run_select(estate, stmt->query, stmt->exact ? 2 : 1, NULL);
  	tuptab = estate->eval_tuptable;
  	n = estate->eval_processed;
  
  	/*
! 	 * If SELECT ... INTO EXACT specified, and the query didn't find exactly
! 	 * 1 row then return.  If EXACT was not specified, then allow the query
! 	 * to find any number of rows.
  	 */
! 	if (stmt->exact && n != 1)
  	{
  		exec_eval_cleanup(estate);
  		return PLPGSQL_RC_OK;
  	}
+ 	else if (n == 0) /* stmt->exact is false */
+ 	{
+ 	     /*
+     	 * null the target
+ 	     */
+     	exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
+ 
+     	exec_eval_cleanup(estate);
+ 
+     	return PLPGSQL_RC_OK;
+ 	}
  
  	/*
  	 * Put the result into the target and set found to true
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.64
diff -c -r1.64 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h	22 Jun 2005 01:35:02 -0000	1.64
--- src/pl/plpgsql/src/plpgsql.h	29 Jul 2005 19:19:56 -0000
***************
*** 449,454 ****
--- 449,455 ----
  {								/* SELECT ... INTO statement		*/
  	int			cmd_type;
  	int			lineno;
+ 	bool		exact;
  	PLpgSQL_rec *rec;
  	PLpgSQL_row *row;
  	PLpgSQL_expr *query;
Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.42
diff -c -r1.42 scan.l
*** src/pl/plpgsql/src/scan.l	26 Jun 2005 19:16:07 -0000	1.42
--- src/pl/plpgsql/src/scan.l	29 Jul 2005 19:19:56 -0000
***************
*** 149,154 ****
--- 149,155 ----
  elseif          { return K_ELSIF;           }
  elsif           { return K_ELSIF;           }
  end				{ return K_END;				}
+ exact		{ return K_EXACT;	    }
  exception		{ return K_EXCEPTION;		}
  execute			{ return K_EXECUTE;			}
  exit			{ return K_EXIT;			}