Обсуждение: 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, itshould return only one row. If it returnsmore than one row, PL/SQLraises the predefined exception TOO_MANY_ROWS.However, if no rows are returned, PL/SQL raisesNO_DATA_FOUND unless theSELECT statement called a SQL aggregate function such as AVG or SUM.(SQL aggregate functionsalways return a value or a null. So, a SELECTINTO statement that calls an aggregate function never raisesNO_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. +
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. +
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. +