Message-ID: <426FCE69.6070805@carvalhaes.net>
Date: Wed, 27 Apr 2005 14:39:53 -0300
From: Rodrigo Carvalhaes <grupos@carvalhaes.net>
Reply-To: grupos@carvalhaes.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: SYNTAX ERROR ON FOR... LOOP
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.