PL/pgsql: use cursor to update next record based on a condition from current record 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;
What you want is something this this with lag() and lead()

UPDATE mytble
SET ownby_ctc = false
FROM (
    SELECT
      id_y_q,
      lead(ownby_ctc) OVER () AS lead_ownby_ctc,
      lead(ownby_ftc) OVER () AS lead_ownby_ctc,
    FROM mytbl
    ORDER BY id, year, qrtr;
) AS t2
WHERE t2.id_y_q = id_y_q
  AND t2.id = id
  AND ownby_ctc = 1
  AND ownby_ftc = 0


I'm not sure your copy works and you don't provide an environment but something like this is what you're after.