Thread: SYNTAX ERROR ON FOR... LOOP

SYNTAX ERROR ON FOR... LOOP

From

Rodrigo Carvalhaes

Date:

27 April 2005, 17:41:17

Hi Guys,

I am having a "simple syntax problem" but very strange...

I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I 
am getting syntax error on this contol structure...
If I comment the IF / ELSE / ENDIF the fuction works ...
I am using PostgreSQL win 8.0
Any tip ? :-D

The function is:

CREATE OR REPLACE FUNCTION reorder() RETURNS int4 AS
$BODY$

DECLARE   new_code     INTEGER;   recs          RECORD;   validation   varchar;   vstrname      varchar;   vstrcgc_cic
varchar;   vstrquery     varchar;   vstrupdate    varchar;
 

BEGIN

new_code := 1;

FOR recs IN (SELECT * FROM table1 ORDER BY is_customer, name)
LOOP

-- Validation
vstrname := replace(recs.name,$$'$$,$$\'$$);

vstrupdate := $$UPDATE table1 SET code = $$ || new_code ||
$$ WHERE old_code = $$ || recs.old_code || $$ AND is_customer $$;

IF records.is_customer IS FALSE THEN
vstrupdate := vstrupdate || $$ IS true $$;
ELSE
vstrupdate := vstrupdate || $$ IS false $$;
ENDIF;

EXECUTE vstrupdate;

new_code := new_code + 1 ;END LOOP;RETURN 1;
END;

$BODY$ LANGUAGE 'plpgsql' VOLATILE;

My table1 is:
   CREATE TABLE table1
( code int4, name varchar(50), old_code int4, is_customer bool DEFAULT false
)
WITHOUT OIDS;

INSERT INTO table1 (code, name, old_code, is_customer) VALUES (9, 
'John', 23, true);
INSERT INTO table1 (code, name, old_code, is_customer) VALUES (19, 
'Rodrigo', 334, true);
INSERT INTO table1 (code, name, old_code, is_customer) VALUES (82, 
'Fulano', 3484, true);
INSERT INTO table1 (code, name, old_code, is_customer) VALUES (1, 
'Beltrano', 3454, false);
INSERT INTO table1 (code, name, old_code, is_customer) VALUES (4, 
'Madicon', 23, false);

Cheers,

Rodrigo Carvalhaes


-- 
Esta mensagem foi verificada pelo sistema de antivírus eacredita-se estar livre de perigo.

Re: SYNTAX ERROR ON FOR... LOOP

From

Michael Fuhr

Date:

27 April 2005, 18:00:23

On Wed, Apr 27, 2005 at 02:39:53PM -0300, Rodrigo Carvalhaes wrote:
> 
> I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I 
> am getting syntax error on this contol structure...

[snip]

> IF records.is_customer IS FALSE THEN
> vstrupdate := vstrupdate || $$ IS true $$;
> ELSE
> vstrupdate := vstrupdate || $$ IS false $$;
> ENDIF;

The above should be "END IF" (with a space).

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: SYNTAX ERROR ON FOR... LOOP

From

George Weaver

Date:

27 April 2005, 18:06:45

Hi Rodrigo,


----- Original Message ----- 
From: "Rodrigo Carvalhaes" <grupos@carvalhaes.net>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 27, 2005 12:39 PM
Subject: [SQL] SYNTAX ERROR ON FOR... LOOP


> Hi Guys,
>
> I am having a "simple syntax problem" but very strange...
>
> I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I 
> am getting syntax error on this contol structure...
> If I comment the IF / ELSE / ENDIF the fuction works ...
>
snip

> vstrupdate := $$UPDATE table1 SET code = $$ || new_code ||
> $$ WHERE old_code = $$ || recs.old_code || $$ AND is_customer $$;
>
> IF records.is_customer IS FALSE THEN
> vstrupdate := vstrupdate || $$ IS true ;$$;                                                         ^^^^
I believe you need an ";" after true and false to complete the string as an 
SQL statement

> ELSE
> vstrupdate := vstrupdate || $$ IS false ;$$;                                                       ^^^^

> ENDIF;
>
> EXECUTE vstrupdate;

Re: SYNTAX ERROR ON FOR... LOOP

From

Tom Lane

Date:

27 April 2005, 18:16:55

Rodrigo Carvalhaes <grupos@carvalhaes.net> writes:
> I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I 
> am getting syntax error on this contol structure...
> If I comment the IF / ELSE / ENDIF the fuction works ...

plpgsql wants "END IF" not "ENDIF".
        regards, tom lane

Re: SYNTAX ERROR ON FOR... LOOP

From

Rodrigo Carvalhaes

Date:

27 April 2005, 19:27:41

Guys, sometimes I make stupid things... <br /><br /> I t was the END IF as all of you said... <br /><br /> Thanks for
yourexists.<br /><br /> Cheers,<br /><br /> Rodrigo<br /><br /> Tom Lane wrote: <blockquote
cite="mid24769.1114625806@sss.pgh.pa.us"type="cite"><pre wrap="">Rodrigo Carvalhaes <a class="moz-txt-link-rfc2396E"
href="mailto:grupos@carvalhaes.net"><grupos@carvalhaes.net></a>writes: </pre><blockquote type="cite"><pre
wrap="">Iam trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I 
 
am getting syntax error on this contol structure...
If I comment the IF / ELSE / ENDIF the fuction works ...   </pre></blockquote><pre wrap="">
plpgsql wants "END IF" not "ENDIF".
        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              <a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faq">http://www.postgresql.org/docs/faq</a>
 </pre></blockquote><br /><pre class="moz-signature" cols="72">-- 


Abraço,

Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br</pre><br />-- <br />Esta mensagem foi verificada pelo sistema de antivírus e <br /> acredita-se
estarlivre de perigo.