Inserting from cursor into a table

Question

Hi I am trying to insert from a cursor into record and with records I am trying to insert into a tables. Find my code below. I create the code to have insertion in different dependent tables. But right now the second loop is going for infinite loop. its not exiting the loop. please find the code below.

CREATE OR REPLACE FUNCTION save_meta_data_test3()
  RETURNS text AS
$BODY$
DECLARE 
     section_seq integer;
     step_seq integer;
     rec_section   RECORD;
     rec_step RECORD;
     i integer;

     cur_section CURSOR 
         FOR select 
                     line_item->>'sectionName' as sectionName,line_item->>'sectionLabel' as sectionLabel,to_number(line_item->>'sectionOrder','99G999D9S') as sectionOrder,line_item->>'outOfScopeSection' as outOfScopeSection,
                     line_item->>'punchListSection' as punchListSection 
                from tbl d,
                         json_array_elements(d.j->'sectionElements') l,
                         json_array_elements(l->'sectionElement') AS line_item
               where tbl_id=3;

     cur_step CURSOR 
               FOR select 
                     line_item2->>'stepName' as stepName,line_item2->>'stepLabel' as stepLabel,to_number(line_item2->>'stepOrder','99G999D9S') as stepOrder,line_item2->>'questionLevelChange',
                     line_item2->>'punchListSection'
               from tbl d,
                        json_array_elements(d.j->'sectionElements') l,
                        json_array_elements(l->'sectionElement') AS line_item,
                        json_array_elements(line_item->'stepElements') AS line_item1,
                        json_array_elements(line_item1->'stepElement') AS line_item2
               where tbl_id=3 ;
BEGIN
 i:=0;
   -- Open the cursor
     OPEN cur_section;

      LOOP
    -- fetch row into the film

        raise notice '%', i;
        i:=i+1;
        FETCH cur_section INTO rec_section;
          raise notice 'section insert';
    -- build the output
          select nextval('checklist_section_id_seq') into section_seq;
          insert into Dummy_section_details(bfs_section_id,bfs_section_name,bfs_section_label,section_order) 
          values(section_seq,rec_section.sectionName,rec_section.sectionLabel,rec_section.sectionOrder);

        OPEN cur_step;
            LOOP
                 FETCH cur_step INTO rec_step;

                    raise notice 'step insert';
                select nextval('ckschema.checklist_step_id_seq') into step_seq;

                Insert into dummy_steps_details(bfs_step_id,bfs_step_name,bfs_step_label,bfs_section_id,step_order)
                    values(step_seq,rec_step.stepName,rec_step.stepLabel,section_seq,rec_step.stepOrder);

                    raise notice 'after step insert';

           EXIT WHEN NOT FOUND;  
        END LOOP;     
         -- LOOP

        CLOSE cur_step;
         raise notice 'after section insert';

     EXIT WHEN NOT FOUND;
   END LOOP;

   -- Close the cursor
   CLOSE cur_section;

   RETURN cur_section.sectionName;
END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Can anyone help on this please..

Answer 1

I had a task similar to this one. I went through the table, took the value from the cursor and wrote to another table with generate new ID.

generate_id() - for generate new unique id;

table_source - table where we take the vale;

table_target - table where we write down;

And then we create function with cursor and save what we need:

CREATE OR REPLACE FUNCTION rewrite_data()
RETURNS void AS
$func$
DECLARE
curs CURSOR FOR SELECT * FROM table_source;
newId long;
BEGIN
FOR row IN curs LOOP

newId = generate_id();
INSERT INTO table_target(id, name, date, other)
VALUES (newId, row.name, row.date, row.other);

END LOOP;
END;
$func$ LANGUAGE plpgsql^
SELECT rewrite_data()^

I hope this post will help someone in the future.