I did some work on your patch:


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

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

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

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.



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

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 3 | next ›
Discussion Overview
grouppgsql-hackers @
postedJun 14, '06 at 1:19p
activeJun 15, '06 at 6:02p

1 user in discussion

Bruce Momjian: 3 posts



site design / logo © 2019 Grokbase