Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT

I did some work on your patch:

ftp://candle.pha.pa.us/pub/postgresql/mypatches/first

I switched the name of the option flag to FIRST (already a reserved
word), making the default behavior PL/SQL-compatible. I also added the
proper execptions to match PL/SQL. My Oracle 9 PL/SQL manual has for
SELECT INTO:

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.)

The big problem is that a lot of applications use the SELECT INTO ... IF
NOT FOUND test, and I don't see any good way to keep those applications
working without being modified.

The #option keyword seems as bad as just giving up on being PL/SQL
compatibile and using the keyword STRICT (already a reserved word) when
you want PL/SQL functionality.

I don't think a GUC is going to work because it will affect all
functions stored in the database, and their might be functions expecting
different behaviors. Setting the GUC in the function that needs it also
will not work because it will spill into functions called by that
function.

I think we set up SELECT INTO this way originally because we didn't have
execeptions, but now that we have them, I don't see a clean way to move
to the PL/SQL behavior. Perhaps STRICT is the best option.

Comments?

---------------------------------------------------------------------------

Matt Miller wrote:
> On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
> > Matt Miller <mattm(at)epx(dot)com> writes:
> > > On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
> > >> I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
> > >> and (b) doesn't seem to convey quite what is happening anyway. Not sure
> > >> about a better word though ... anyone?
> >
> > > I can attach a patch that supports [EXACT | NOEXACT].
> >
> > Somehow, proposing two new reserved words instead of one doesn't seem
> > very responsive to my gripe :-(.
>
> My intention was to introduce the idea that the current behavior should
> be changed, and to then suggest a path that eventually eliminates all
> the new reserved words.
>
> > If you think that this should be a global option instead of a
> > per-statement one, something like the (undocumented) #option hack might
> > be a good way to specify it; that would give it per-function scope,
> > which seems reasonable.
> >
> > create function myfn(...) returns ... as $$
> > #option select_into_1_row
> > declare ...
> > $$ language plpgsql;
> >
>
> Thanks, I'll take a look at this.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +