Horton

Thread

  1. PL/pgSQL: SELECT INTO EXACT

    Matt Miller <mattm@epx.com> — 2005-07-29T20:18:14Z

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

  2. Re: PL/pgSQL: SELECT INTO EXACT

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-07-29T21:52:55Z

    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

  3. Re: PL/pgSQL: SELECT INTO EXACT

    Jaime Casanova <systemguards@gmail.com> — 2005-07-29T22:04:52Z

    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 ;)

  4. Re: PL/pgSQL: SELECT INTO EXACT

    Matt Miller <mattm@epx.com> — 2005-07-29T22:43:13Z

    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?

  5. Re: PL/pgSQL: SELECT INTO EXACT

    Matt Miller <mattm@epx.com> — 2005-07-29T22:47:18Z

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

  6. Re: PL/pgSQL: SELECT INTO EXACT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-07-30T04:23:57Z

    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

  7. Re: PL/pgSQL: SELECT INTO EXACT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-07-30T04:24:25Z

    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

  8. Re: PL/pgSQL: SELECT INTO EXACT

    Matt Miller <mattm@epx.com> — 2005-08-08T21:01:09Z

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

  9. Re: PL/pgSQL: SELECT INTO EXACT

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-08-08T21:18:53Z

    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

  10. Re: PL/pgSQL: SELECT INTO EXACT

    Matt Miller <mattm@epx.com> — 2005-08-08T21:45:54Z

    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.

  11. Re: PL/pgSQL: SELECT INTO EXACT

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-08-09T00:06:13Z

    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

  12. Re: PL/pgSQL: #option select_into_1_row (was SELECT INTO

    Matt Miller <mattm@epx.com> — 2005-08-09T15:01:29Z

    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?

  13. Re: PL/pgSQL: #option select_into_1_row (was SELECT INTO

    Matt Miller <mattm@epx.com> — 2005-08-11T15:04:36Z

    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?

  14. Re: PL/pgSQL: #option select_into_1_row (was SELECT INTO

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-08-11T15:24:45Z

    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

  15. Re: PL/pgSQL: SELECT INTO EXACT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-08-13T01:53:25Z

    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

  16. Re: PL/pgSQL: #option select_into_1_row (was SELECT INTO

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-08-13T02:42:36Z

    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

  17. Re: PL/pgSQL: SELECT INTO EXACT

    Matt Miller <mattm@epx.com> — 2005-08-15T14:03:38Z

    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.

  18. Re: [PATCHES] PL/pgSQL: #option select_into_1_row (was SELECT INTO

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-03-21T03:23:36Z

    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.

Are we agreed this is the way we want to control this?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

  19. Re: [PATCHES] PL/pgSQL: #option select_into_1_row (was SELECT INTO

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-21T03:30:39Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Are we agreed this is the way we want to control this?

AFAICT, no one except Matt wants this feature at all, so I'd much prefer
to drop the whole idea.  I think it's a insufficiently justified kluge.

			regards, tom lane

  20. Re: [PATCHES] PL/pgSQL: #option select_into_1_row (was SELECT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-03-21T03:32:12Z

    Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Are we agreed this is the way we want to control this?
> 
> AFAICT, no one except Matt wants this feature at all, so I'd much prefer
> to drop the whole idea.  I think it's a insufficiently justified kluge.

Fine.  Unless I hear from the non-kludge croud, it is dropped.  ;-)

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

  21. Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-14T13:18:31Z

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

  22. Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-14T21:23:11Z

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

  23. Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-15T18:02:12Z

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