Thread: pl/pgsql oddity
Hello everyone,
When writing some serverside code I ran into an oddity that I
managed to boil down to this:
-------------------------------------------------------
create or replace function fubar() returns varchar as '
declare
l integer;
begin
l = 38;
if l < 38 then
return ''< 38'';
elseif l >= 38 then
return ''>= 38'';
else
return ''this is not possible'';
end if;
end;'
language 'plpgsql';
-------------------------------------------------------
But I can't understand or solve it. The function always returns
'this is not possible'. Can someone tell me what I'm overlooking?
Thanks!
Hi, > l = 38; This should be: l := 38; otherwise l would remain uninitialized (NULL). Ciao ciao -- Matteo Beccati http://phpadsnew.com http://phppgads.com
On Thu, 16 Dec 2004 10:06:19 +0100 (CET), Joolz <joolz@arbodienst-limburg.nl> wrote: > Hello everyone, > > When writing some serverside code I ran into an oddity that I > managed to boil down to this: > > ------------------------------------------------------- > create or replace function fubar() returns varchar as ' > declare > l integer; > begin > l = 38; > if l < 38 then > return ''< 38''; > elseif l >= 38 then Try "elsif" here. (No, I don't know what the problem with "elseif" is). Ian Barwick
Joolz wrote:
> Hello everyone,
>
> When writing some serverside code I ran into an oddity that I
> managed to boil down to this:
> elseif l >= 38 then
You want "elsif" - plpgsql isn't a hugely sophisticated language and its
parser is having trouble there. I'm guessing the parser is somehow
putting the "elseif" branch under the initial "then" so it never gets
executed. If you rewrite the function like so:
create or replace function fubar() returns varchar as '
declare
l integer;
begin
l = 34;
if l < 38 then
raise notice ''< 38: %'',l;
elseif l >= 38
then raise notice ''>= 38: %'',l;
else
raise notice ''this is not possible: %'',l;
end if;
return 0;
end;'
language 'plpgsql';
Now, try different values for "l" and you'll see what is happening.
Congratulations - I think you've found a bug. You can report it formally
via the bugs mailing list or http://www.postgresql.org/bugform.html
--
Richard Huxton
Archonet Ltd
> When writing some serverside code I ran into an oddity that I
> managed to boil down to this:
>
> -------------------------------------------------------
> create or replace function fubar() returns varchar as '
> declare
> l integer;
> begin
> l = 38;
> if l < 38 then
> return ''< 38'';
> elseif l >= 38 then
> return ''>= 38'';
> else
> return ''this is not possible'';
> end if;
> end;'
> language 'plpgsql';
> -------------------------------------------------------
>
> But I can't understand or solve it. The function always returns
> 'this is not possible'. Can someone tell me what I'm overlooking?
> Thanks!
Documentation says you should use "elsif" rather than "elseif". In your
case everything between "return <38" and "else" is discarded as
unreachable code. The same sense will have function below:
create or replace function fubar() returns varchar as '
declare
l integer;
begin
l = 38;
if l < 38 then
return ''< 38'';
bleblbebleblebe sfdsdf;
nothing special;
else
return ''this is not possible'';
end if;
end;'
language 'plpgsql';
select fubar();
Regards,
Tomasz Myrta
Ian Barwick zei: > On Thu, 16 Dec 2004 10:06:19 +0100 (CET), Joolz > <joolz@arbodienst-limburg.nl> wrote: >> Hello everyone, >> >> When writing some serverside code I ran into an oddity that I >> managed to boil down to this: >> >> ------------------------------------------------------- >> create or replace function fubar() returns varchar as ' >> declare >> l integer; >> begin >> l = 38; >> if l < 38 then >> return ''< 38''; >> elseif l >= 38 then > > Try "elsif" here. > (No, I don't know what the problem with "elseif" is). Thanks Ian, but I don't understand... I _am_ already using elseif...
Tomasz Myrta zei: >> When writing some serverside code I ran into an oddity that I >> managed to boil down to this: >> >> ------------------------------------------------------- >> create or replace function fubar() returns varchar as ' >> declare >> l integer; >> begin >> l = 38; >> if l < 38 then >> return ''< 38''; >> elseif l >= 38 then >> return ''>= 38''; >> else >> return ''this is not possible''; >> end if; >> end;' >> language 'plpgsql'; >> ------------------------------------------------------- >> >> But I can't understand or solve it. The function always returns >> 'this is not possible'. Can someone tell me what I'm overlooking? >> Thanks! > > Documentation says you should use "elsif" rather than "elseif". In > your > case everything between "return <38" and "else" is discarded as > unreachable code. That's it, thanks!
Richard Huxton zei:
Hi Richard,
See the other posting,
elseif l >= 38
Apparently this is parsed as
elseif l >= 38
^ ^
| |
code|
|
comment from here on
It should be "elsif", not "elseif" :-\
Thanks everyone!
Richard Huxton wrote: > You want "elsif" - plpgsql isn't a hugely sophisticated language and its > parser is having trouble there. I'm guessing the parser is somehow > putting the "elseif" branch under the initial "then" so it never gets > executed. Indeed; the parser thinks an unrecognized keyword indicates the beginning of a SQL statement: since the PL/PgSQL parser and the SQL parser are completely separate, we need to do some guessing about what constitutes a legal SQL statement. See the more detailed diagnose of the problem here: http://archives.postgresql.org/pgsql-bugs/2004-11/msg00297.php There's a patch in that thread that provides better PL/PgSQL error checking (which results in flagging this kind of code as invalid at compile time). Some form of that patch will be in 8.1, as well as other nice stuff like warning for unreachable code. Tom also suggested just adding 'elseif' as an alternative for 'elsif'. That sounds like it would be worth doing. > Congratulations - I think you've found a bug. You can report it formally > via the bugs mailing list No need, this is a known issue. -Neil
Matteo Beccati wrote: > Hi, > >> l = 38; > > > This should be: > > l := 38; > > otherwise l would remain uninitialized (NULL). Actually, either work. You are right that the docs suggest the second form though. -- Richard Huxton Archonet Ltd
Neil Conway <neilc@samurai.com> writes:
> Tom also suggested just adding 'elseif' as an alternative for 'elsif'.
> That sounds like it would be worth doing.
I think we should go ahead and do that for 8.0. I'm getting tired of
reading reports that stem from this mistake (I think this is the third
one in the past month ...). I can't see any real downside to accepting
both spellings, can you?
regards, tom lane
Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: > >>Tom also suggested just adding 'elseif' as an alternative for 'elsif'. >>That sounds like it would be worth doing. > > > I think we should go ahead and do that for 8.0. I'm getting tired of > reading reports that stem from this mistake (I think this is the third > one in the past month ...). I can't see any real downside to accepting > both spellings, can you? Well, you end up with the opposite problem. Someone has a issue and is focusing on the spelling of elseif (elsif), because the google searches they've done happen to come up with a different spelling then what they're using. So, they're looking for a solution in the wrong place. I don't know of any other language that permits multiple spellings for the same construct. I'd be concerned with starting such a precedent. Now I'm sure someone will post back with some valid examples from other languages. :( -- Until later, Geoffrey
On Thu, Dec 16, 2004 at 12:27:53PM -0500, Geoffrey wrote: > I don't know of any other language that permits multiple spellings for > the same construct. I'd be concerned with starting such a precedent. I'd be in favor of making it a bloody law that every bloody language use the same bloody spelling. I'm forever forgetting whether a particular language uses ELSE IF, ELSEIF, ELSIF, or ELIF. Grumble, grumble, grumble.... -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Geoffrey <esoteric@3times25.net> writes:
> I don't know of any other language that permits multiple spellings for
> the same construct. I'd be concerned with starting such a precedent.
Well, we have plenty of precedent already at the SQL language level:
ANALYZE vs ANALYSE, NOTNULL vs IS NOT NULL, and so on.
regards, tom lane
Tom Lane wrote: > Geoffrey <esoteric@3times25.net> writes: > >>I don't know of any other language that permits multiple spellings for >>the same construct. I'd be concerned with starting such a precedent. > > > Well, we have plenty of precedent already at the SQL language level: > ANALYZE vs ANALYSE, NOTNULL vs IS NOT NULL, and so on. Like I said, someone would come up with some real world examples. :) I personally wouldn't lose too much sleep over it. -- Until later, Geoffrey
Michael Fuhr wrote: > On Thu, Dec 16, 2004 at 12:27:53PM -0500, Geoffrey wrote: > >> I don't know of any other language that permits multiple spellings >> for the same construct. I'd be concerned with starting such a >> precedent. > > I'd be in favor of making it a bloody law that every bloody language > use the same bloody spelling. I'm forever forgetting whether a > particular language uses ELSE IF, ELSEIF, ELSIF, or ELIF. Grumble, > grumble, grumble.... As a relative newbie to PostgreSQL (but an old-timer to programming languages and other DMBSs) I would certainly vote for allowing elseif. This is my first encounter of "else" without the terminating "e", and that would not be a natural omission for me. -- Guy Rouillier
--- Tom Lane <tgl@sss.pgh.pa.us> escreveu: > Neil Conway <neilc@samurai.com> writes: > > Tom also suggested just adding 'elseif' as an alternative for 'elsif'. > > That sounds like it would be worth doing. > > I think we should go ahead and do that for 8.0. I'm getting tired of > reading reports that stem from this mistake (I think this is the third > one in the past month ...). I can't see any real downside to accepting > both spellings, can you? > > regards, tom lane I made the exact same mistake and it took me a good chunk of energy to figure it out. I didn't report it to the list. When one look at the manual it is very easy to read elseif in instead of elsif. Regards, Clodoaldo Pinto _______________________________________________________ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/
On Thu, Dec 16, 2004 at 05:14:42PM -0300, Clodoaldo Pinto wrote: > --- Tom Lane <tgl@sss.pgh.pa.us> escreveu: > > Neil Conway <neilc@samurai.com> writes: > > > Tom also suggested just adding 'elseif' as an alternative for 'elsif'. > > > That sounds like it would be worth doing. > > > > I think we should go ahead and do that for 8.0. I'm getting tired of > > reading reports that stem from this mistake (I think this is the third > > one in the past month ...). I can't see any real downside to accepting > > both spellings, can you? > > > > regards, tom lane > > I made the exact same mistake and it took me a good chunk of energy to figure > it out. I didn't report it to the list. When one look at the manual it is very > easy to read elseif in instead of elsif. I'd vote for one of two options: 1. Accept both elseif and elsif as equivalent or 2. Add some parsing magic to give a meaningful error message so people see it immediatly. Maybe just match in "elseif" at beginning of a line and error out with "maybe you meant elsif". -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
"elsif" is the spelling used by Ada. I'm getting rather used to it, myself. I'm really starting to like Ada. So "elsif" is fine with me. As far as alternate spellings being accepted within a language, look at the Transcript language used by Runtime Revolution (www.runrev.com), which is a so-called "Xtalk" language (based on HyperTalk, which was used by HyperCard; and yes, HyperTalk was like this too...) -- and # are synonyms (comment delimiters) abbreviated can be abbreviated as abbr or abbrev ;-) audioClip can be abbreviated as ac accelKey and acceleratorKey acceleratorModifiers and accelMods arm and armed autoHilite and autoHighlight recentNames and backList group, background, bg, and bkgnd The list goes on and On and ON... On Dec 16, 2004, at 2:12 PM, Guy Rouillier wrote: > Michael Fuhr wrote: >> On Thu, Dec 16, 2004 at 12:27:53PM -0500, Geoffrey wrote: >> >>> I don't know of any other language that permits multiple spellings >>> for the same construct. I'd be concerned with starting such a >>> precedent. >> >> I'd be in favor of making it a bloody law that every bloody language >> use the same bloody spelling. I'm forever forgetting whether a >> particular language uses ELSE IF, ELSEIF, ELSIF, or ELIF. Grumble, >> grumble, grumble.... > > As a relative newbie to PostgreSQL (but an old-timer to programming > languages and other DMBSs) I would certainly vote for allowing elseif. > This is my first encounter of "else" without the terminating "e", and > that would not be a natural omission for me. > > -- > Guy Rouillier > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match > > ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
I prefer the FORTRAN66 construct
IF <condition> <label1> <label2> <label3>
where it jumps to label1 if <condition> is negative, label2 if zero, and
label3 if positive. No else ifs about it.
I hope you realize I'm as kidding as I am obviously too old.
Michael Fuhr
<mike@fuhr.org> To: Geoffrey <esoteric@3times25.net>
Sent by: cc: pgsql-general@postgresql.org
pgsql-general-owner@pos Subject: Re: [GENERAL] pl/pgsql oddity
tgresql.org
12/16/2004 01:16 PM
On Thu, Dec 16, 2004 at 12:27:53PM -0500, Geoffrey wrote:
> I don't know of any other language that permits multiple spellings for
> the same construct. I'd be concerned with starting such a precedent.
I'd be in favor of making it a bloody law that every bloody language
use the same bloody spelling. I'm forever forgetting whether a
particular language uses ELSE IF, ELSEIF, ELSIF, or ELIF. Grumble,
grumble, grumble....
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 3: 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
Don't get used to Ada. It's almost as dead as COBOL, though I liked it too
for some things. Oracle plsql is soooo Ada-like I've literally cut and
pasted whole Ada routines into Oracle plsql and they work without
modification. PostgreSQL doesn't do parameters and packages, so it is
slightly more work to move stuff from your front-end into your back-end,
but often worth it.
"Frank D. Engel, Jr."
<fde101@fjrhome.net> To: pgsql-general <pgsql-general@postgresql.org>
Sent by: cc:
pgsql-general-owner@pos Subject: Re: [GENERAL] pl/pgsql oddity
tgresql.org
12/16/2004 03:01 PM
"elsif" is the spelling used by Ada. I'm getting rather used to it,
myself.
I'm really starting to like Ada. So "elsif" is fine with me.
As far as alternate spellings being accepted within a language, look at
the Transcript language used by Runtime Revolution (www.runrev.com),
which is a so-called "Xtalk" language (based on HyperTalk, which was
used by HyperCard; and yes, HyperTalk was like this too...)
-- and # are synonyms (comment delimiters)
abbreviated can be abbreviated as abbr or abbrev ;-)
audioClip can be abbreviated as ac
accelKey and acceleratorKey
acceleratorModifiers and accelMods
arm and armed
autoHilite and autoHighlight
recentNames and backList
group, background, bg, and bkgnd
The list goes on and On and ON...
On Dec 16, 2004, at 2:12 PM, Guy Rouillier wrote:
> Michael Fuhr wrote:
>> On Thu, Dec 16, 2004 at 12:27:53PM -0500, Geoffrey wrote:
>>
>>> I don't know of any other language that permits multiple spellings
>>> for the same construct. I'd be concerned with starting such a
>>> precedent.
>>
>> I'd be in favor of making it a bloody law that every bloody language
>> use the same bloody spelling. I'm forever forgetting whether a
>> particular language uses ELSE IF, ELSEIF, ELSIF, or ELIF. Grumble,
>> grumble, grumble....
>
> As a relative newbie to PostgreSQL (but an old-timer to programming
> languages and other DMBSs) I would certainly vote for allowing elseif.
> This is my first encounter of "else" without the terminating "e", and
> that would not be a natural omission for me.
>
> --
> Guy Rouillier
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>
>
-----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Thu, Dec 16, 2004 at 15:34:03 -0500, Richard_D_Levine@raytheon.com wrote: > I prefer the FORTRAN66 construct > > IF <condition> <label1> <label2> <label3> My memory is that those labels were separated by commas. > > where it jumps to label1 if <condition> is negative, label2 if zero, and > label3 if positive. No else ifs about it. > > I hope you realize I'm as kidding as I am obviously too old. As long as you are dredging up old Fortran; in my opinion computed gotos were harder to follow than the ones based on the sign of an expression.
Since when is COBOL dead ;-) Can't say I know that language yet, actually (I did know some Fortran, but it's been a while)... Just because a language is not in common use does not mean it lacks value. Some ideas are so far ahead of their time, they get laughed off for a time, then suddenly become so widespread no one would believe they were ever looked down on. (The world *is* flat, right?) On Dec 16, 2004, at 4:18 PM, Richard_D_Levine@raytheon.com wrote: > Don't get used to Ada. It's almost as dead as COBOL, though I liked > it too > for some things. Oracle plsql is soooo Ada-like I've literally cut and > pasted whole Ada routines into Oracle plsql and they work without > modification. PostgreSQL doesn't do parameters and packages, so it is > slightly more work to move stuff from your front-end into your > back-end, > but often worth it. > > > > "Frank D. Engel, Jr." > <fde101@fjrhome.net> To: > pgsql-general <pgsql-general@postgresql.org> > Sent by: cc: > pgsql-general-owner@pos Subject: Re: > [GENERAL] pl/pgsql oddity > tgresql.org > > > 12/16/2004 03:01 PM > > > > > > > "elsif" is the spelling used by Ada. I'm getting rather used to it, > myself. > > I'm really starting to like Ada. So "elsif" is fine with me. > > As far as alternate spellings being accepted within a language, look at > the Transcript language used by Runtime Revolution (www.runrev.com), > which is a so-called "Xtalk" language (based on HyperTalk, which was > used by HyperCard; and yes, HyperTalk was like this too...) > > -- and # are synonyms (comment delimiters) > > abbreviated can be abbreviated as abbr or abbrev ;-) > > audioClip can be abbreviated as ac > > accelKey and acceleratorKey > > acceleratorModifiers and accelMods > > arm and armed > > autoHilite and autoHighlight > > recentNames and backList > > group, background, bg, and bkgnd > > > The list goes on and On and ON... > > > On Dec 16, 2004, at 2:12 PM, Guy Rouillier wrote: > >> Michael Fuhr wrote: >>> On Thu, Dec 16, 2004 at 12:27:53PM -0500, Geoffrey wrote: >>> >>>> I don't know of any other language that permits multiple spellings >>>> for the same construct. I'd be concerned with starting such a >>>> precedent. >>> >>> I'd be in favor of making it a bloody law that every bloody language >>> use the same bloody spelling. I'm forever forgetting whether a >>> particular language uses ELSE IF, ELSEIF, ELSIF, or ELIF. Grumble, >>> grumble, grumble.... >> >> As a relative newbie to PostgreSQL (but an old-timer to programming >> languages and other DMBSs) I would certainly vote for allowing elseif. >> This is my first encounter of "else" without the terminating "e", and >> that would not be a natural omission for me. >> >> -- >> Guy Rouillier >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: the planner will ignore your desire to choose an index scan if >> your >> joining column's datatypes do not match >> >> > ----------------------------------------------------------- > Frank D. Engel, Jr. <fde101@fjrhome.net> > > $ ln -s /usr/share/kjvbible /usr/manual > $ true | cat /usr/manual | grep "John 3:16" > John 3:16 For God so loved the world, that he gave his only begotten > Son, that whosoever believeth in him should not perish, but have > everlasting life. > $ > > > > ___________________________________________________________ > $0 Web Hosting with up to 120MB web space, 1000 MB Transfer > 10 Personalized POP and Web E-mail Accounts, and much more. > Signup at www.doteasy.com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
On Thu, 2004-12-16 at 11:09 -0500, Tom Lane wrote: > I think we should go ahead and do that for 8.0. I'm getting tired of > reading reports that stem from this mistake (I think this is the third > one in the past month ...). I can't see any real downside to accepting > both spellings, can you? I agree this is pretty harmless. I've applied the attached trivial patch to HEAD. -Neil
Attachment
I blew the parenthesis around the conditional expression also. Hence the
*old* comment.
(Embedded image moved to file: pic14771.jpg)
Bruno Wolff III
<bruno@wolff.to> To: Richard_D_Levine@raytheon.com
cc: Michael Fuhr <mike@fuhr.org>, Geoffrey
<esoteric@3times25.net>,
12/16/2004 06:13 pgsql-general@postgresql.org, pgsql-general-owner@postgresql.org
PM Subject: Re: pl/pgsql oddity
On Thu, Dec 16, 2004 at 15:34:03 -0500,
Richard_D_Levine@raytheon.com wrote:
> I prefer the FORTRAN66 construct
>
> IF <condition> <label1> <label2> <label3>
My memory is that those labels were separated by commas.
>
> where it jumps to label1 if <condition> is negative, label2 if zero, and
> label3 if positive. No else ifs about it.
>
> I hope you realize I'm as kidding as I am obviously too old.
As long as you are dredging up old Fortran; in my opinion computed
gotos were harder to follow than the ones based on the sign of an
expression.
