-1

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;
3

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 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.