Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3

• The function works... All the data is updated as expected. However, when I call the function for the second time, it touches the rows that had already been touched by the previous call....

• It triplicate ( LIMIT 3 ) the records.

Question:

How can I make the function to gets the next 3 rows and not use the same rows that have been used before?

Function updated:

CREATE or REPLACE FUNCTION function_data_1()
RETURNS SETOF bigint AS $$

declare       row record;

BEGIN

-- copying the data to the backup table (not the blobs)
-- Limiting in 5000 rows each call
FOR row IN EXECUTE '           SELECT                   t1.file_id           FROM                   table1 t1           JOIN                   table3 t3 ON t3.file_id = t1.file_id           ORDER BY 1 LIMIT 3 ' 
LOOP

-- Creating the backup table with the essential data
INSERT INTO table2 (note_id, size, file_id, full_path)   (           SELECT                   t1.note_id,                   t1.size,                   t1.file_id,                   t1.full_path           FROM                   table1 t1           JOIN                   table3 t3 ON t3.file_id = t1.file_id           WHERE                   t1.file_id = row.file_id   );

-- copying the blobs to the table above table2       UPDATE junk.table2 t2 SET data =       (           SELECT                   o1.data           FROM                   original_table1_b o1           JOIN                   table3 t3 ON t3.file_id = o1.file_id           WHERE                   t3.migrated = 0           AND                   t2.file_id = o1.file_id           AND                   o1.file_id = row.file_id       )       WHERE t2.file_id = row.file_id;

-- updating the migrated column from 0 to 1       UPDATE               table3 t2       SET               migrated = 1       WHERE               t2.file_id = row.file_id       AND               migrated = 0;

-- set the blobs as null       UPDATE               original_table1_b o1       SET               data = NULL       WHERE               o1.file_id = row.file_id;
END LOOP;

END

$$ language 'plpgsql';