1

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 Feb 2 '17 at 18:11
  • @EvanCarroll Thank you for your comment, I will check lag() function – Eyla Feb 2 '17 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 Feb 2 '17 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 Feb 2 '17 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 Feb 2 '17 at 19:21
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.