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

it is capital in rec_film.sectionName, sectionname is the colum name for dummy table which I am trying to insert — Rose, Sep 23 '15 at 08:17
That's very confusing, you should probably use consistent case everywhere in your code. — machine yearning, Sep 23 '15 at 08:40
@machineyearning: `sectionName` and `sectionname` are identical identifiers in SQL — , Sep 23 '15 at 08:43
You don't need a cursor nor a loop. This can be done ***much*** more efficiently with just a single statement: `insert into dummy_table (...) select line_item .... from ..` The error that you get, stems from the fact that your columns in the select list don't have an alias. You need to use an alias for each column, e.g. `line_item->>'sectionName' as sectioname` otherwise the column gets some system generated name — , Sep 23 '15 at 08:44
yes but I need to iterate it as I will have n number of steps for section and in each step I will have n number of questions. So I beleive we need cursor — Rose, Sep 23 '15 at 08:46
@a_horse_with_no_name That's not true at all. Keywords in SQL are case-insensitive but column names may or may not be depending on the implementation, installation, configuration, OS, etc. [See this question](https://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive). In PostgreSQL they are case-sensitive, [although mostly only technically](https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive). However it's still a good coding practice to stick to one casing choice per language throughout your code base if possible. — machine yearning, Sep 23 '15 at 08:51
Also I'm just speculating here but @Rose your problem may have something to do with your using a column name as a declared identifier, in the variable `sectionName`. You should really avoid doing that, in addition to mixing your case rules it's another practice that will lead to you having confusing and bug-prone code. — machine yearning, Sep 23 '15 at 08:58
I would say your next step in debugging should be to see exactly what the field/column names are in `rec_film` just before you encounter the error. — machine yearning, Sep 23 '15 at 09:24
@machineyearning: in SQL column names (identifiers) are ***only*** case sensitive if they are enclosed in double quotes. So `"sectionName"` is indeed something different than `sectionName`. But this *never* depends on the configuration. Rose's problem is that the expression `line_item->>'sectionName' ` does not get the "column name" `sectionName` unless an alias is used — , Sep 23 '15 at 09:59
@a_horse_with_no_name. How can I give alias as I am fetching the value fron json by giving alias. I tried to give a name by seperating with comma.but no luck. — Rose, Sep 23 '15 at 10:27
As I have written in my comment: `select line_item->>'sectionName' AS sectionName, line_item->>'sectionLabel' AS sectionLabel, ....` — , Sep 23 '15 at 10:31
@a_horse_with_no_name could you please cite a source for your assertions? According to [SQL92](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt): section 5.2.13 the question of whether a comparison between a regular identifier and a quoted identifier has anything to do with case is implementation-dependent and depends on the collation configuration. I admit that according to SQL92 you are correct RE: unquoted identifiers, however in my experience implementations do not always follow the standard, for example MySQL in the past depended on the host OS for unquoted identifier collation. — machine yearning, Sep 23 '15 at 11:50
@machineyearning: MySQL doesn't care about standards anyway - and this question is about Postgres (and there, just as well as in Oracle, DB2 and Firebird, this does **not** depend on the configuration). You need to read section 5.2 carefully. It requires that collation to be case sensitive: "*and an implementation-defined collation that **is sensitive to case**". The collation itself is "implementation defined", but it has to be case sensitive (for delimited identifiers) — , Sep 23 '15 at 11:56
@a_horse_with_no_name: you're absolutely right, I concede that point. — machine yearning, Sep 23 '15 at 11:58
@a_horse_with_no_name: Anyway my main point was that to avoid even having to think about stuff like this you should use clearly distinct names for all your identifiers, and if using some sort of procedural superset of SQL you should certainly at least avoid mixing your schema's column, table, etc names with identifiers in your procedural code. And as the original source of my confusion, you certainly shouldn't just use case to distinguish, since in this case it's not only a bad practice to mix case rules, but in fact could cause you to reuse identifiers without realizing it. — machine yearning, Sep 23 '15 at 12:01
@a_horse_with_no_name I am able to fetch value as you said. Thanks a lot.. — Rose, Sep 23 '15 at 12:40

score 4 · Answer 1 · answered May 11 '18 at 12:00

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.

Yes...it is most useful. I had a requirement where going through this solved my need!! Thanks a lot. — dbusern, Sep 15 '18 at 13:54

Inserting from cursor into a table

1 Answers1