nested elseif woes

Dear Ivan:

On Mon, 10 May 2004, Ivan Sergio Borgonovo wrote:

First thanks to Tom Lane who helped me promptly.

Now let's come to the problem:

create or replace function testa( )
returns char(32) as
'
begin
if 1=2 then
if 1=2 then
select 1;
elseif 1=3 then
select 2;
elseif 1=4 then
if 1=5 then
select 3;
else
select 4;
end if;
end if;
end if;
return md5(''aaaa'');
end;
' language plpgsql;

test1=# select * from testa();
ERROR: syntax error at or near "if"
CONTEXT: compile of PL/pgSQL function "testa" near line 14

I made several test functions with similar structure with no
improvements.
I can't even figure a pattern. I get errors on if, else, and elseif.
Till now the culprit seems to be elseif. Whenever I write test
functions without elseif I get no errors.
Did I misinterpreted the docs (37.7.2.4)?

I really can't see the problem.

I'm running 7.4.2-1 on Debian Sarge.

Is this the literal function? If so, try changing the "elseif" to "elsif".
No 2nd 'e'.

-frank

On Mon, 10 May 2004, Ivan Sergio Borgonovo wrote:

Now let's come to the problem:

create or replace function testa( )
returns char(32) as
'
begin
if 1=2 then
if 1=2 then
select 1;
elseif 1=3 then
select 2;
elseif 1=4 then

Don't you mean ELSIF, not ELSEIF?

Kris Jurka

Ivan Sergio Borgonovo wrote:

First thanks to Tom Lane who helped me promptly.

Now let's come to the problem:

create or replace function testa( )
returns char(32) as
'
begin
if 1=2 then
if 1=2 then
select 1;
elseif 1=3 then
select 2;
elseif 1=4 then
if 1=5 then
select 3;
else
select 4;
end if;
end if;
end if;
return md5(''aaaa'');
end;
' language plpgsql;

test1=# select * from testa();
ERROR: syntax error at or near "if"
CONTEXT: compile of PL/pgSQL function "testa" near line 14

This is odd, I replaced the else ifs with elsif and it worked on 7.4. My
7.3 documentation says that else if and elsif are equivalent.
imp=# select * from testa();
testa
----------------------------------
74b87337454200d4d33f80c4663dc5e5
(1 row)

My test code (yours, slightly modified):
drop function testa( );

create or replace function testa( )
returns char(32) as
'
begin
if 1=2 then
if 1=2 then
select 1;
elsif 1=3 then
select 2;
elsif 1=4 then
if 1=5 then
select 3;
else
select 4;
end if;
end if;
end if;
return md5(''aaaa'');
end;
' language plpgsql;

select * from testa();

I made several test functions with similar structure with no
improvements.
I can't even figure a pattern. I get errors on if, else, and elseif.
Till now the culprit seems to be elseif. Whenever I write test
functions without elseif I get no errors.
Did I misinterpreted the docs (37.7.2.4)?

I really can't see the problem.

I'm running 7.4.2-1 on Debian Sarge.

To be less annoying to the list, could anyone point me to somewhere
where I could look at functions written by others.
Any good project with enough complicated functions to be useful to
learn.

thx

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Ron

On Mon, 10 May 2004 13:56:39 -0500 (EST)
Kris Jurka <books@ejurka.com> wrote:

On Mon, 10 May 2004, Ivan Sergio Borgonovo wrote:

Now let's come to the problem:

create or replace function testa( )
returns char(32) as
'
begin
if 1=2 then
if 1=2 then
select 1;
elseif 1=3 then
select 2;
elseif 1=4 then

Don't you mean ELSIF, not ELSEIF?

thanks to everyone.
Curiously enough, trying to figure out what was wrong with my code,
I've been able to write versions with the wrong spelling that didn't
complain. That brought me astray.

Anyway does anyone know any public big enough project written in
plpgsql from which I could learn lurking at the code?

On Tue, May 11, 2004 at 12:58:55AM +0200, Ivan Sergio Borgonovo wrote:

thanks to everyone.
Curiously enough, trying to figure out what was wrong with my code,
I've been able to write versions with the wrong spelling that didn't
complain. That brought me astray.

Anyway does anyone know any public big enough project written in
plpgsql from which I could learn lurking at the code?

OpenACS (from openacs.org) is a huge web application framework with
the vast majority of the business logic written in PL/SQL. It has
a wide selection of PL/SQL functions, with equivalents for both
Oracle and PostgreSQL.

Cheers,
Steve

On 11/05/04, Ivan Sergio Borgonovo (mail@webthatworks.it) wrote:

Anyway does anyone know any public big enough project written in
plpgsql from which I could learn lurking at the code?

Hi Ivan

I've written a small demo app in PHP and postgres. Whether or not you
use php, the bugadb.php file shows different ways of calling the plpgsql
functions.

You can see a (slightly out of date) version of the small demo running
here:

http://campbell-lange.net/bugaboo/

You can download the source from the bottom of my index page under the
link "Download the 24K zip file of bugaboo sources".

Bear in mind that I'm not an expert. However we have been doing webapps
for the last 2.5 years with a huge amount of success using plpgsql
functions handling the spatial arrangement of data and perl or php to do
the formatting and data verification.

Comments and corrections gratefully received.

Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>