Re: [HACKERS] [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 [EMAIL PROTECTED] 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 EnterpriseDBhttp://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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://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 EnterpriseDBhttp://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
Re: [HACKERS] [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, 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 [EMAIL PROTECTED] 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 EnterpriseDBhttp://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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [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 [EMAIL PROTECTED] 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 EnterpriseDBhttp://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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://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
