Tom Lane wrote: > [EMAIL PROTECTED] (Bruce Momjian) writes: > > 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. > > I've got a couple of problems with the error codes used by this patch. > In the first place, you can't arbitrarily assign names to error > conditions that are different from the standard spelling (see > errcodes.sgml for why not: the standard spellings are what are > documented). In the second place, the spec clearly says that class 02
I saw this at the top of plerrcodes.h: * Eventually this header file should be auto-generated from errcodes.h * with some sort of sed hackery, but no time for that now. It's likely * that an exact mapping will not be what's wanted anyhow ... so I figured we were supposed to map them. > is warning conditions, not errors, so using ERRCODE_NO_DATA for an error > is inappropriate. Oh, I see that now: /* Class 02 - No Data --- this is also a warning class per SQL99 */ /* (do not use this class for failure conditions!) */ #define ERRCODE_NO_DATA MAKE_SQLSTATE('0','2', '0','0','0') > Where did you get those names from ... were they picked out of the air, > or were they intended to be Oracle-compatible, or what? Surely we I pulled this from the Oracle documentation that I quoted earlier: > > When you use a SELECT INTO statement without the BULK COLLECT clause, it > > should return only one row. If it returns more than one row, PL/SQL > > raises the predefined exception TOO_MANY_ROWS. > > > > However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the > > SELECT statement called a SQL aggregate function such as AVG or SUM. > > (SQL aggregate functions always return a value or a null. So, a SELECT > > INTO statement that calls an aggregate function never raises > > NO_DATA_FOUND.) Are those both errors in Oracle? I assumed so. > aren't trying to be Oracle-compatible at that level of detail (else > we've doubtless got a huge number of other cases where we throw a > different error than they do). I thought it was nice to get as close as possible, but using a warning code is clearly bad. > Do we actually need different error codes for too few and too many rows? > It looks to me like the only relevant standard error condition is > CARDINALITY_VIOLATION, so either we throw CARDINALITY_VIOLATION for both > cases or we invent nonstandard codes. We could, and then suggest using ROW_COUNT to determine if there were too few rows, or too many. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster