PL/pgsql: use cursor to update next record based on a condition from current record

Question

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;

I think this could be done with a single UPDATE ... FROM ( SELECT lag() ORDER BY id, year, qrtr ) — Evan Carroll
– Evan Carroll, Commented Feb 2, 2017 at 18:11
@EvanCarroll Thank you for your comment, I will check lag() function — Eyla
– Eyla, Commented Feb 2, 2017 at 18:15
So in this syntax, you're setting ownby_ctc = false for that criteria in the loop? There is a lot of conditional statements that don't look kosher to me. This isn't a valid function ` r1.ownby_ctc = AND r1.ownby_ftc =0` — Evan Carroll
– Evan Carroll, Commented Feb 2, 2017 at 18:51
@EvanCarroll I typed it by mistake (r1.ownby_ctc = AND r1.ownby_ftc =0) and I updated the correct condition. I tested the folow of the function and it seems ok for me. — Eyla
– Eyla, Commented Feb 2, 2017 at 19:07
I still don't think this works. How could r1.ownby_ctc = 0 ever be true if the whole thing is wrapped in r1.ownby_ctc = 1 — Evan Carroll
– Evan Carroll, Commented Feb 2, 2017 at 19:21

Evan Carroll · Accepted Answer · 2017-02-02 19:22:25Z

3

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.

answered Feb 2, 2017 at 19:22

Evan Carroll

65.8k50 gold badges263 silver badges512 bronze badges

Add a comment |

Stack Exchange Network

PL/pgsql: use cursor to update next record based on a condition from current record

1 Answer 1

Your Answer

Hot Network Questions

PL/pgsql: use cursor to update next record based on a condition from current record

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions