Postgresql cant declare cursor

Question

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;

Answer 1

You are working with cursor in the wrong way.

FETCH [ direction { FROM | IN } ] cursor INTO target;

Documentation.

As you can see you missed INTO clause.

So the right way should be:

OPEN djb_row; -- Do not forget to open your cursor
FETCH NEXT FROM djb_row INTO
    jac_block_id,
    jack_dc_id,
    jack_block_start,
    jack_block_stop,
    jack_dc_name;

(of course the number and order of fields/variables should be same)

Or just use the record variable to hold the cursor's content:

DECLARE r record;
....
OPEN djb_row; -- Again: do not forget to open it
FETCH NEXT FROM djb_row INTO r;
WHILE hex_to_int(r.jack_block_stop) > hex_to_int(r.jack_block_start)  LOOP ...