I am trying to run a simple plsql in Netezza, to run a loop on a result set of select query, here is the below code I am trying to run
CREATE OR REPLACE PROCEDURE UPDATE_SUSPECT_GROUP_ID()
RETURNS CHARACTER VARYING(ANY) EXECUTE AS CALLER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
VAL BIGINT := 100000000000000;
BEGIN
RAISE NOTICE 'lets start';
FOR r IN
select
suspect_id as suspect_id
from apollo_customer_analysis.ASHKUMAR.REFINED_SUSPECT
LOOP
RAISE NOTICE 'val: %',r.suspect_id;
END LOOP;
END;
END_PROC;
EXECUTE UPDATE_SUSPECT_GROUP_ID();
When try to run the code, I get the below error
ERROR [01000] NOTICE: plpgsql: ERROR during compile of UPDATE_SUSPECT_GROUP_ID near line 15
ERROR [01000] NOTICE: line 1 at execute statement
ERROR [HY000] ERROR: syntax error, unexpected ';', expecting LOOP at or near ";"
Can someone help me how this error could be resolved?