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

5 files changed, 49 insertions, 11 deletions

diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 5343dfb1978..21a0bd6541e 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.91 2006/06/12 16:45:30 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.92 2006/06/15 18:02:22 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -157,6 +157,7 @@ static	void			 check_labels(const char *start_label,
 %token	K_ELSE
 %token	K_ELSIF
 %token	K_END
+%token	K_STRICT
 %token	K_EXCEPTION
 %token	K_EXECUTE
 %token	K_EXIT
@@ -2001,6 +2002,7 @@ make_select_stmt(void)
 	PLpgSQL_rec			*rec = NULL;
 	int					tok;
 	bool				have_into = false;
+	bool				have_strict = false;
 
 	plpgsql_dstring_init(&ds);
 	plpgsql_dstring_append(&ds, "SELECT ");
@@ -2028,6 +2030,11 @@ make_select_stmt(void)
 						 errmsg("INTO specified more than once")));
 			}
 			tok = yylex();
+			if (tok == K_STRICT)
+			{
+				have_strict = true;
+				tok = yylex();
+			}
 			switch (tok)
 			{
 				case T_ROW:
@@ -2108,6 +2115,7 @@ make_select_stmt(void)
 		select->rec		 = rec;
 		select->row		 = row;
 		select->query	 = expr;
+		select->strict	 = have_strict;
 
 		return (PLpgSQL_stmt *)select;
 	}
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 3ac48bbcecb..893ad1dfdc8 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.170 2006/06/12 16:45:30 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.171 2006/06/15 18:02:22 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1701,23 +1701,41 @@ exec_stmt_select(PLpgSQL_execstate *estate, PLpgSQL_stmt_select *stmt)
 
 	/*
 	 * Run the query
+	 * Retrieving two rows can be slower than a single row, e.g. 
+	 * a sequential scan where the scan has to be completed to
+	 * check for a second row.  For this reason, we only do the
+	 * second-line check for STRICT.
 	 */
-	exec_run_select(estate, stmt->query, 1, NULL);
+	exec_run_select(estate, stmt->query, stmt->strict ? 2 : 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 SELECT ... INTO specified STRICT, and the query didn't
+	 * find exactly one row, throw an error.  If STRICT was not specified,
+	 * then allow the query to find any number of rows.
 	 */
 	if (n == 0)
 	{
-		exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
-		exec_eval_cleanup(estate);
-		return PLPGSQL_RC_OK;
+		if (!stmt->strict)
+		{
+			/* null the target */
+			exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
+			exec_eval_cleanup(estate);
+			return PLPGSQL_RC_OK;
+		}
+		else
+			ereport(ERROR,
+				(errcode(ERRCODE_NO_DATA),
+				 errmsg("query returned no rows")));
 	}
+	else if (n > 1 && stmt->strict)
+		ereport(ERROR,
+			(errcode(ERRCODE_CARDINALITY_VIOLATION),
+			 errmsg("query more than one row")));
 
 	/*
-	 * Put the result into the target and set found to true
+	 * Put the first result into the target and set found to true
 	 */
 	exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
 	exec_set_found(estate, true);
diff --git a/src/pl/plpgsql/src/plerrcodes.h b/src/pl/plpgsql/src/plerrcodes.h
index be9f764a9ae..fedd4d0c7e7 100644
--- a/src/pl/plpgsql/src/plerrcodes.h
+++ b/src/pl/plpgsql/src/plerrcodes.h
@@ -9,7 +9,7 @@
  *
  * Copyright (c) 2003-2006, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/pl/plpgsql/src/plerrcodes.h,v 1.7 2006/03/05 15:59:10 momjian Exp $
+ * $PostgreSQL: pgsql/src/pl/plpgsql/src/plerrcodes.h,v 1.8 2006/06/15 18:02:22 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -722,3 +722,13 @@
 {
 	"index_corrupted", ERRCODE_INDEX_CORRUPTED
 },
+
+{
+	"no_data_found", ERRCODE_NO_DATA
+},
+
+{
+	"too_many_rows", ERRCODE_CARDINALITY_VIOLATION
+},
+
+
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 16ffe7e93df..9f2e7b0a057 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.75 2006/06/12 16:45:30 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.76 2006/06/15 18:02:22 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -432,6 +432,7 @@ typedef struct
 {								/* SELECT ... INTO statement		*/
 	int			cmd_type;
 	int			lineno;
+	bool		strict;
 	PLpgSQL_rec *rec;
 	PLpgSQL_row *row;
 	PLpgSQL_expr *query;
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index daafe96b874..1dca30a5666 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.50 2006/06/12 16:45:30 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.51 2006/06/15 18:02:22 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -129,6 +129,7 @@ else			{ return K_ELSE;			}
 elseif          { return K_ELSIF;           }
 elsif           { return K_ELSIF;           }
 end				{ return K_END;				}
+strict			{ return K_STRICT;	    	}
 exception		{ return K_EXCEPTION;		}
 execute			{ return K_EXECUTE;			}
 exit			{ return K_EXIT;			}