pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSYNTAX ERROR ON FOR... LOOP
5+ messages / 4 participants
[nested] [flat]
* SYNTAX ERROR ON FOR... LOOP
@ 2005-04-27 17:39 Rodrigo Carvalhaes <grupos@carvalhaes.net>
0 siblings, 3 replies; 5+ messages in thread
From: Rodrigo Carvalhaes @ 2005-04-27 17:39 UTC (permalink / raw)
To: pgsql-sql
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 e
acredita-se estar livre de perigo.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SYNTAX ERROR ON FOR... LOOP
@ 2005-04-27 18:00 Michael Fuhr <mike@fuhr.org>
parent: Rodrigo Carvalhaes <grupos@carvalhaes.net>
2 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-04-27 18:00 UTC (permalink / raw)
To: Rodrigo Carvalhaes <grupos@carvalhaes.net>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SYNTAX ERROR ON FOR... LOOP
@ 2005-04-27 18:04 George Weaver <gweaver@shaw.ca>
parent: Rodrigo Carvalhaes <grupos@carvalhaes.net>
2 siblings, 0 replies; 5+ messages in thread
From: George Weaver @ 2005-04-27 18:04 UTC (permalink / raw)
To: grupos@carvalhaes.net; pgsql-sql
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SYNTAX ERROR ON FOR... LOOP
@ 2005-04-27 18:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rodrigo Carvalhaes <grupos@carvalhaes.net>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-04-27 18:16 UTC (permalink / raw)
To: grupos@carvalhaes.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SYNTAX ERROR ON FOR... LOOP
@ 2005-04-27 19:27 Rodrigo Carvalhaes <grupos@carvalhaes.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Rodrigo Carvalhaes @ 2005-04-27 19:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Guys, sometimes I make stupid things... <br>
<br>
I t was the END IF as all of you said... <br>
<br>
Thanks for your exists.<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="">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 ...
</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>
</body>
<br />--
<br />Esta mensagem foi verificada pelo sistema de antivírus e
<br /> acredita-se estar livre de perigo.
</html>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-27 19:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-27 17:39 SYNTAX ERROR ON FOR... LOOP Rodrigo Carvalhaes <grupos@carvalhaes.net>
2005-04-27 18:00 ` Michael Fuhr <mike@fuhr.org>
2005-04-27 18:04 ` George Weaver <gweaver@shaw.ca>
2005-04-27 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-27 19:27 ` Rodrigo Carvalhaes <grupos@carvalhaes.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox