This patch implements an optional EXACT keyword after the INTO keyword
of the PL/pgSQL SELECT INTO command.  The motivation is to come closer
to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets,
raise an exception and leave the targets untouched if the query does not
return exactly one row.  This patch does not go so far as to raise an
exception, but it can simplify porting efforts from PL/SQL.  I also feel
that this EXACT behavior is overall a bit cleaner than the current
PL/pgSQL behavior.  Maybe I've just been brainwashed by years of
Oracle'ing.

Here are three excerpts from the patched PL/pgSQL documentation:

"If the EXACT option is specified, then target will not be set unless
the query returns exactly one row"

"You can check the special FOUND variable after a SELECT INTO to
determine whether the statement was successful. ... an EXACT query is
successful only if exactly 1 row is returned."

"...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a
SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2,
indicating no matching rows, exactly one matching row, or greater than
one matching row, respectively."
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;			}
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Reply via email to