I have this function in Postgresql 9.6 using pl/pgsql. This function is to update a row from a table based on a condition on previous row.
The table has Item id(id), year of ownership (year) , quarter of ownership(quarter), own by ctc status(ownby_ctc), and own by ftc status (ownby_ftc).
Each item id will be repeated one time each quarter every year from 2013 to 2016. ownby_ctc will be marked 1 if item owned by CTC and it is either: 1- Happened before ownby_ftc or 2- Happened in the same quarter as ownby_ftc and ownby_ctc never happened after ownby_ftc.
In case, ownby_ctc happened before ownby_ftc
I want to select the row where ownby_ctc = 1 and ownby_ftc = 0 and update the column ownby_ctc in next rows to be 1 until ownby_ftc is equal to 1.
Notice: * the table is order by id, year, quarter. *primary key is id_y_q. Problem with my code is that the function is updating one row if the condition is true and this is because I am updating the actual table but not the cursor.
Please advice how to solve this problem.
CREATE OR REPLACE FUNCTION myfun() RETURNS VOID AS $$ DECLARE cur SCROLL CURSOR FOR select id_y_q, id, year, qrtr, ownby_ctc, ownby_ftc FROM mytbl ORDER BY id, year, qrtr; r1 RECORD; r2 RECORD; BEGIN OPEN cur ; LOOP FETCH cur INTO r1; EXIT WHEN NOT FOUND; IF (r1.ownby_ctc = 1 and r1.ownby_ftc =0 )THEN MOVE FORWARD 0 FROM cur; FETCH cur INTO r2; EXIT WHEN NOT FOUND; IF (r1.id = r2.id) THEN IF ( r1.ownby_ctc = 0 AND r1.ownby_ftc =0 ) OR (r1.ownby_ctc = 0 and r1.ownby_ftc =1 ) THEN UPDATE mytbl SET r1.ownby_ctc = 0 = 1 WHERE id_y_q = r2.id_y_q ; MOVE BACKWARD 1 FROM cur; END IF; END IF; END IF; END LOOP; CLOSE cur; END; $$ LANGUAGE plpgsql;