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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 14, '06 at 1:19p
activeJun 15, '06 at 6:02p
posts3
users1
websitepostgresql.org...
irc#postgresql

1 user in discussion

Bruce Momjian: 3 posts

People

Translate

site design / logo © 2017 Grokbase