Thread: PL/pgSQL: SELECT INTO EXACT
This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. I also feel that this EXACT behavior is overall a bit cleaner than the current PL/pgSQL behavior. Maybe I've just been brainwashed by years of Oracle'ing. Here are three excerpts from the patched PL/pgSQL documentation: "If the EXACT option is specified, then target will not be set unless the query returns exactly one row" "You can check the special FOUND variable after a SELECT INTO to determine whether the statement was successful. ... an EXACT query is successful only if exactly 1 row is returned." "...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2, indicating no matching rows, exactly one matching row, or greater than one matching row, respectively."
Attachment
Matt Miller <mattm@epx.com> writes:
> This patch implements an optional EXACT keyword after the INTO keyword
> of the PL/pgSQL SELECT INTO command. The motivation is to come closer
> to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets,
> raise an exception and leave the targets untouched if the query does not
> return exactly one row. This patch does not go so far as to raise an
> exception, but it can simplify porting efforts from PL/SQL.
Uh, what's the point of being only sort-of compatible? Why not throw
the exception?
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?
regards, tom lane
On 7/29/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Matt Miller <mattm@epx.com> writes: > > This patch implements an optional EXACT keyword after the INTO keyword > > of the PL/pgSQL SELECT INTO command. The motivation is to come closer > > to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, > > raise an exception and leave the targets untouched if the query does not > > return exactly one row. This patch does not go so far as to raise an > > exception, but it can simplify porting efforts from PL/SQL. > > Uh, what's the point of being only sort-of compatible? Why not throw > the exception? > > 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? > > regards, tom lane > just wonder, why that is not the default behavior of the SELECT INTO? at least, the first time i think the function was right until i found that the first row of a set of rows was assigned... i mean, when you do that code you are expecting just one row from your query, doesn't you? -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote: > Matt Miller <mattm@epx.com> writes: > > The motivation is to come closer to Oracle's SELECT INTO > > behavior: when SELECTing INTO scalar targets, > > raise an exception and leave the targets untouched if the query does > > not return exactly one row. This patch does not go so far as > > to raise an exception > Uh, what's the point of being only sort-of compatible? Why not throw > the exception? I guess my hesitation is that the PL/SQL notion of the exception as a program flow control technique is a bit at odds with the PL/pgSQL notion of the exception as a transaction control mechanism. Maybe these notions could be reconciled by a new NOSAVE option to the EXCEPTION block definition, to suppress the savepoint and the exception-induced rollback for that BEGIN ... END block. Then an automatically-thrown exception would not be so expensive. > 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 The motivation is that EXACTly one row must be returned. Maybe UNIQUE instead of EXACT?
> > The motivation is to come closer > > to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, > > raise an exception and leave the targets untouched if the query does not > > return exactly one row. > why that is not the default behavior of the SELECT INTO? > ... > i mean, when you do that code you are expecting just one row from your > query I agree. I suppose I was fearful of breaking existing stuff, so I added a new keyword.
This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Matt Miller wrote:
> This patch implements an optional EXACT keyword after the INTO keyword
> of the PL/pgSQL SELECT INTO command. The motivation is to come closer
> to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets,
> raise an exception and leave the targets untouched if the query does not
> return exactly one row. This patch does not go so far as to raise an
> exception, but it can simplify porting efforts from PL/SQL. I also feel
> that this EXACT behavior is overall a bit cleaner than the current
> PL/pgSQL behavior. Maybe I've just been brainwashed by years of
> Oracle'ing.
>
> Here are three excerpts from the patched PL/pgSQL documentation:
>
> "If the EXACT option is specified, then target will not be set unless
> the query returns exactly one row"
>
> "You can check the special FOUND variable after a SELECT INTO to
> determine whether the statement was successful. ... an EXACT query is
> successful only if exactly 1 row is returned."
>
> "...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a
> SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2,
> indicating no matching rows, exactly one matching row, or greater than
> one matching row, respectively."
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Sorry, patch removed from the queue. I now see the later discussion. --------------------------------------------------------------------------- Matt Miller wrote: > This patch implements an optional EXACT keyword after the INTO keyword > of the PL/pgSQL SELECT INTO command. The motivation is to come closer > to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, > raise an exception and leave the targets untouched if the query does not > return exactly one row. This patch does not go so far as to raise an > exception, but it can simplify porting efforts from PL/SQL. I also feel > that this EXACT behavior is overall a bit cleaner than the current > PL/pgSQL behavior. Maybe I've just been brainwashed by years of > Oracle'ing. > > Here are three excerpts from the patched PL/pgSQL documentation: > > "If the EXACT option is specified, then target will not be set unless > the query returns exactly one row" > > "You can check the special FOUND variable after a SELECT INTO to > determine whether the statement was successful. ... an EXACT query is > successful only if exactly 1 row is returned." > > "...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a > SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2, > indicating no matching rows, exactly one matching row, or greater than > one matching row, respectively." [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote: > Matt Miller <mattm@epx.com> writes: > > This patch implements an optional EXACT keyword after the INTO keyword > > of the PL/pgSQL SELECT INTO command. ... when SELECTing INTO ... > > leave the targets untouched if the query does not > > return exactly one row. > > 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 don't know how to avoid adding a keyword, unless the proposed EXACT behavior just replaces the current behavior, potentially breaking existing code. Is there a precedent for language-specific GUC vars? I think the EXACT behavior is more reasonable overall, and maybe a stepped approach can replace the current behavior with the EXACT flavor. To that end the option could support either EXACT or NOEXACT, with NOEXACT initially being the default. Eventually EXACT could become the default, and finally the NOEXACT option could be dropped altogether. At that point the EXACT keyword would be dropped as well. I can attach a patch that supports [EXACT | NOEXACT].
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 :-(.
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;
regards, tom lane
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.
Matt Miller <mattm@epx.com> writes:
>>> 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.
Once you put 'em in, you can't ever really get rid of 'em :-( ... so I'd
prefer to investigate a path that doesn't use that syntax in the first
place.
regards, tom lane
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote: > 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; Attached is a patch that implements the "#option select_into_1_row" directive as suggested. Is it time to document this directive?
Attachment
On Tue, 2005-08-09 at 15:01 +0000, Matt Miller wrote: > Attached is a patch that implements the "#option select_into_1_row" > directive as suggested. Is this patch good-to-go? Can it be queued?
Matt Miller wrote: > On Tue, 2005-08-09 at 15:01 +0000, Matt Miller wrote: > > Attached is a patch that implements the "#option select_into_1_row" > > directive as suggested. > > Is this patch good-to-go? Can it be queued? We are in feature freeze, so unless there is overwhelming community support, it will be held for 8.2. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Tom Lane wrote:
> Matt Miller <mattm@epx.com> writes:
> >>> 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.
>
> Once you put 'em in, you can't ever really get rid of 'em :-( ... so I'd
> prefer to investigate a path that doesn't use that syntax in the first
> place.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Matt Miller wrote:
> On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
> > 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;
>
> Attached is a patch that implements the "#option select_into_1_row"
> directive as suggested.
>
> Is it time to document this directive?
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, 2005-08-12 at 21:53 -0400, Bruce Momjian wrote: > This has been saved for the 8.2 release: Just to clarify: the "SELECT INTO EXACT" patch was abandoned in favor of the "#option select_into_1_row" patch. I submitted both patches as part of the same -patches thread, but the latter solution, the "#option select_into_1_row" patch, superseded the "SELECT INTO EXACT" idea. The correct patch is at http://archives.postgresql.org/pgsql-patches/2005-08/msg00070.php This should be the only patch that gets applied.