From: Rodrigo Carvalhaes <grupos@carvalhaes.net>
To: pgsql-sql@postgresql.org
Subject: SYNTAX ERROR ON FOR... LOOP
Date: Wed, 27 Apr 2005 14:39:53 -0300
Message-ID: <426FCE69.6070805@carvalhaes.net> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grupos@carvalhaes.net
Subject: Re: SYNTAX ERROR ON FOR... LOOP
In-Reply-To: <426FCE69.6070805@carvalhaes.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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