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

I have update the patch at:

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

I re-did it to use STRICT for Oracle PL/SQL syntax. I don't think we
are going to be able to do any better than that, even in future
versions. I added documentation that should help too.

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

Bruce Momjian wrote:
>
> 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. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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

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