I am trying to declare select statement to cursor and then declare fields from Cursor to variables and then loop over them (the loop is fine , I have problem with cursor declaration ) having syntax errors on cursor . any ideas?
CREATE OR REPLACE FUNCTION LoadLoop() RETURNS integer AS $$
DECLARE jac_block_id numeric(22);
DECLARE jack_dc_id numeric(22);
DECLARE jack_block_start text;-- :='A921050202AB24';
DECLARE jack_block_stop text; -- :='A921050202AB6B';
DECLARE jack_dc_name text;
DECLARE djb_row CURSOR FOR select * from stg_dim_jack_blocks;
BEGIN
FETCH NEXT FROM djb_row
jac_block_id: = djb_row.jac_block_id;
jack_dc_id: = djb_row.jack_dc_id;
jack_block_start: = djb_row.jack_block_start;
jack_block_stop: = djb_row.jack_block_stop;
jack_dc_name: = djb_row.jack_dc_name;
WHILE hex_to_int(jack_block_stop) > hex_to_int(jack_block_start) LOOP
insert into stg_dim_jack_blocks_serial_no
values (jac_block_id,to_hex(hex_to_int(jack_block_start)),jack_dc_id,jack_dc_name,clock_timestamp());
jack_block_start :=to_hex(hex_to_int(jack_block_start)+1);
END LOOP;
CLOSE djb_row;
RETURN 0;
END;
$$ LANGUAGE plpgsql;