From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [COMMITTERS] pgsql: Add STRICT to PL/pgSQL SELECT INTO, so exceptions |
Date: | 2006-06-16 19:47:39 |
Message-ID: | 200606161947.k5GJleV24504@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers pgsql-hackers |
Tom Lane wrote:
> momjian(at)postgresql(dot)org (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. +
From | Date | Subject | |
---|---|---|---|
Next Message | User Dezelin | 2006-06-16 19:49:46 | pgguid - pgguid: deleting project files |
Previous Message | Tom Lane | 2006-06-16 19:36:34 | Re: [COMMITTERS] pgsql: Add STRICT to PL/pgSQL SELECT INTO, so exceptions are thrown if |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2006-06-16 19:57:58 | Re: A real currency type |
Previous Message | Tom Lane | 2006-06-16 19:36:34 | Re: [COMMITTERS] pgsql: Add STRICT to PL/pgSQL SELECT INTO, so exceptions are thrown if |