Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT - Mailing list pgsql-hackers

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@epx.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@postgresql.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. +