CODEWITHSUNDEEP
postgresqlplpgsql
damike
damike

Reputation: 345

How to reflect updates in a loop?

I have a migration run. At each iteration i can update several rows. But how can i skip already updated rows? Changes inside the loop are not visible to the outside (yes I know - stable result) - but how can i change that? I want, that if a person already has a graph_id --> SKIP

DROP TABLE IF EXISTS persons_2_persons, persons, graph;

CREATE TABLE graph (id SERIAL PRIMARY KEY);
CREATE TABLE persons (id SERIAL PRIMARY KEY, graph_id INTEGER REFERENCES graph(id));
CREATE TABLE persons_2_persons("from" INTEGER NOT NULL REFERENCES persons(id), "to" INTEGER NOT NULL REFERENCES persons(id));

INSERT INTO persons (graph_id) VALUES (NULL), (NULL), (NULL);

INSERT INTO persons_2_persons VALUES
(1,2),(2,1),
(2,3),(3,2);

-- Table has about 18 Mio records. 30-40 persons are connected --> goal: give each graph/cluster an graph_id
-- I also tried with CURSORs

DO $x$
DECLARE  
  var_record RECORD;
  var_graph_id INTEGER;
BEGIN
    
    FOR var_record IN SELECT * FROM persons -- Reevaluate after each iteration
    LOOP
        IF var_record.graph_id IS NULL THEN -- this should be false at the 2nd and 3nd iteration because we updated the data in 1st iteration
          INSERT INTO graph DEFAULT VALUES RETURNING id INTO var_graph_id;
          RAISE NOTICE '%', var_graph_id;
        
          UPDATE persons SET graph_id = var_graph_id WHERE id IN (1,2,3); -- (1,2,3) is found by a RECURESIVE CTE. This are normally 30-40 persons
         
        END IF;
    END LOOP;
END;
$x$;

SELECT * FROM persons;

Upvotes: 0

Views: 256

Answers (1)

jjanes
jjanes

Reputation: 44343

Make the first command in the loop refresh that individual record:

select * from persons into var_record where id=var_record.id;

Upvotes: 1

Related Questions