How to update source table inside for loop body in postgresql

Question

I have a table through which I'm iterating a for loop and while trying to update the source table data inside the for loop, it's not reflecting. In the below example I want the RASIE NOTICE statement should execute 5 times but it's only executing 3 times.

DO $$
DECLARE p_Id int;

BEGIN
    DROP TABLE IF EXISTS TempTbl;
    CREATE TEMPORARY TABLE TempTbl (Id int);
    INSERT INTO TempTbl VALUES (1);
    INSERT INTO TempTbl VALUES (2);
    INSERT INTO TempTbl VALUES (3);    
        
    FOR p_Id in SELECT * FROM TempTbl
    LOOP        
        RAISE NOTICE 'Value of p_Id: %', p_Id;

        INSERT INTO TempTbl
        SELECT 4
        WHERE 4 NOT IN (SELECT Id FROM TempTbl);
        
        INSERT INTO TempTbl
        SELECT 5
        WHERE 5 NOT IN (SELECT Id FROM TempTbl);      
    END LOOP;
    
END $$;

Current Output

NOTICE:  Value of p_Id: 1
NOTICE:  Value of p_Id: 2
NOTICE:  Value of p_Id: 3

Expected Output

NOTICE:  Value of p_Id: 1
NOTICE:  Value of p_Id: 2
NOTICE:  Value of p_Id: 3
NOTICE:  Value of p_Id: 4
NOTICE:  Value of p_Id: 5

Note: I would be retrieving the values 4 and 5 from the first 3 rows (i.e., I can't insert all the values at the beginning).

Your current output is not the ***real*** current output, see [db<>fiddle](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dfc2018a1ea06e823312403b60cb38fd). Please add details on what you want to achieve. — Shmiel, Jul 05 '22 at 15:32
This `SELECT * FROM TempTbl` is only going to executed once, so all you are going to see are the values when you first run it. Since there are 3 values you will insert 4 and 5 three times. — Adrian Klaver, Jul 05 '22 at 16:41
@AdrianKlaver Then any guess that how can I handle this situation. In my use case, I have to insert the records (4 and 5) inside the for loop and have to execute the for loop block for 4 and 5. — SUBHAJIT GANGULI, Jul 05 '22 at 18:17
Why do you have to work in the loop? Why not just `INSERT INTO TempTbl SELECT 4 WHERE 4 NOT IN (SELECT Id FROM TempTbl);` ,etc as lines after the original three `INSERT`'s? — Adrian Klaver, Jul 05 '22 at 18:45
You **cannot** get 4, 5 in the same loop. The `for ...` statement is a [cursor for](https://www.postgresql.org/docs/14/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP) loop. It will contain every row from the table when initially executed. But it executes only on the first iteration and cannot expand. **Any DML in the loop is not reflected in the cursor.** If you must have the rows inserted during the loop you can create a second loop with 'for select ... where p_id > 3 ...`. — Belayer, Jul 05 '22 at 18:45

score 0 · Answer 1 · answered Jul 09 '22 at 23:45

The loop seems to be based on a misunderstanding. Either way, this is how it could work:

DO
$do$
DECLARE
   p_id int;
   val4 int;
   val5 int;
BEGIN
   DROP TABLE IF EXISTS pg_temp.tbl;
   CREATE TEMPORARY TABLE tbl (id int PRIMARY KEY);
   INSERT INTO tbl VALUES (1), (2), (3);    

   FOR p_id IN
      SELECT * FROM tbl
   LOOP        
      RAISE NOTICE 'Value of p_id: %', p_id;
      -- compute values val4 & val5
      -- which, oddly, seems to require a loop?
      -- I doubt that
   END LOOP;

   -- this somehow happens in the loop
   val4 := 4;
   val5 := 5;

   INSERT INTO tbl VALUES (val4) ON CONFLICT DO NOTHING;
   IF FOUND THEN
      RAISE NOTICE 'Value of p_id: %', val4;
   ELSE
      RAISE NOTICE 'Value of p_id: % (failed with unique violation)', val4;
   END IF;

   INSERT INTO tbl VALUES (val5) ON CONFLICT DO NOTHING;
   IF FOUND THEN
      RAISE NOTICE 'Value of p_id: %', val5;
   ELSE
      RAISE NOTICE 'Value of p_id: % (failed with unique violation)', val5;
   END IF;
END
$do$;

Avoid NOT IN (SELECT ...) unless you know exactly what you are doing. See:

Select rows which are not present in other table

And avoid CaMeL-case identifiers. See:

Are PostgreSQL column names case-sensitive?

How to update source table inside for loop body in postgresql

1 Answers1