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:
! SELECT INTO target select_expressions FROM ...;
where target can be a record variable, a row
--- 1067,1073 ----
variable, or list of scalar variables. This is done by:
! SELECT INTO EXACT target select_expressions FROM ...;
where target can be a record variable, a row
***************
*** 1108,1126 ****
! 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 the first row is not well-defined unless you've
! used ORDER BY.)
! You can check the special FOUND variable (see
! ) after a
! SELECT INTO statement to determine whether the
! assignment was successful, that is, at least one row was was returned by
! the query. For example:
SELECT INTO myrec * FROM emp WHERE empname = myname;
--- 1108,1130 ----
! If the EXACT option is specified, then
! target will not be set unless the query
! returns exactly one row. If EXACT is not
! specified then target will be set
! regardless of the number of rows returned by the query. In the
! non-EXACT 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 the first row is not
! well-defined unless you've used ORDER BY.)
! You can check the special FOUND variable after a
! SELECT INTO to determine whether the statement was
! successful. A non-EXACT query is considered successful
! if any rows are returned, and an EXACT query is
! successful only if exactly 1 row is returned. For example:
SELECT INTO myrec * FROM emp WHERE empname = myname;
***************
*** 1128,1141 ****
RAISE EXCEPTION 'employee % not found', myname;
END IF;
! To test for whether a record/row result is null, you can use the
! IS NULL 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:
DECLARE
users_rec RECORD;
--- 1132,1196 ----
RAISE EXCEPTION 'employee % not found', myname;
END IF;
+
+
+ SELECT INTO EXACT myrec * FROM emp WHERE empname = myname;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'employee % not found or not unique', myname;
+ END IF;
+
+
+
+
+ When using the EXACT option you can distinguish the
+ not-found case from the not-unique case by using
+ GET DIAGNOSTICS (see
+ ) to retrieve
+ ROW_COUNT. After a
+ SELECT INTO EXACT statement
+ ROW_COUNT 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:
+
+
+ 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;
+
! To test for whether a record/row result is null, you can use the
! IS NULL conditional. Keep in mind that a result
! might be null for any of a number of reasons:
!
!
!
! The query could return a row with a null value.
!
!
!
!
! A non-EXACT query could return no rows and set all targets
! to null.
!
!
!
!
! An EXACT 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 SELECT INTO EXACT
!
!
!
! Here is an IS NULL example:
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 ****
! A SELECT INTO statement sets
! FOUND true if it returns a row, false if no
! row is returned.
--- 1458,1468 ----
! A SELECT INTO statement without the
! EXACT option sets FOUND
! true if it finds at least one row. A
! SELECT INTO EXACT sets
! FOUND true if it finds exactly one row.
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; }