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

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