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

Patch applied. Thanks.

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

Bruce Momjian wrote:
>
> 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. +
>
> ---------------------------(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. +