Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
От | Bruce Momjian |
---|---|
Тема | Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT |
Дата | |
Msg-id | 200606151802.k5FI2CA22922@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | 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. +
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: Remove the limit on the number of entries allowed in catcaches,