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(at)arbodienst-limburg(dot)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

Ian Barwick zei:
> On Thu, 16 Dec 2004 10:06:19 +0100 (CET), Joolz
> <joolz(at)arbodienst-limburg(dot)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...

> 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

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(at)samurai(dot)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(at)samurai(dot)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(at)3times25(dot)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(at)3times25(dot)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

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escreveu:
> Neil Conway <neilc(at)samurai(dot)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(at)sss(dot)pgh(dot)pa(dot)us> escreveu:
> > Neil Conway <neilc(at)samurai(dot)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(at)svana(dot)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.

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