Update data based on the previous row in postgresql

  CREATE TABLE xyz (name text, cols int, rows int, minitemcols int, x int, y int, rn int);
  INSERT INTO xyz ("widgetName",cols,"rows","minItemCols",x,y,rn) VALUES
     ('A',12,19,8,0,0,1),
     ('B',12,11,12,NULL,NULL,2),
     ('C',5,6,4,NULL,NULL,3),
     ('D',3,6,3,NULL,NULL,4),
     ('E',4,6,3,NULL,NULL,5),
     ('F',4,6,4,NULL,NULL,6),
     ('G',3,6,3,NULL,NULL,7),
     ('H',5,6,5,NULL,NULL,8),
     ('I',7,6,7,NULL,NULL,9),
     ('J',12,9,12,NULL,NULL,10)
     ('K',12,24,8,NULL,NULL,11),
     ('L',5,16,3,NULL,NULL,12),
     ('M',7,16,7,NULL,NULL,13);

-- x
IF (CurrentRow.cols + CurrentRow.x = 12) || (CurrentRow.cols + CurrentRow.x = NextRow.minItemCols) THEN 
    NextRow.x = 0
ELSE IF (CurrentRow.cols + CurrentRow.x < 12) && (CurrentRow.cols + CurrentRow.x >= NextRow.minItemCols)
    NextRow.x = CurrentRow.cols + CurrentRow.x
ELSE IF (CurrentRow.cols + CurrentRow.x < 12)
    NextRow.x = CurrentRow.cols + CurrentRow.x
END IF;    


--y
IF (CurrentRow.cols + CurrentRow.x = 12) THEN 
    NextRow.y = CurrentRow.rows + CurrentRow.y
ELSE IF (CurrentRow.cols + CurrentRow.x < 12)
    NextRow.y = CurrentRow.y
END IF;   
-- Output (providing in insert statements)

INSERT INTO xyz ("widgetName",cols,"rows","minItemCols",x,y,rn) VALUES
     ('A',12,19,8,0,0,1),
     ('B',12,11,12,0,19,2),
     ('C',5,6,4,0,30,3),
     ('D',3,6,3,5,30,4),
     ('E',4,6,3,8,30,5),
     ('F',4,6,4,0,36,6),
     ('G',3,6,3,4,36,7),
     ('H',5,6,5,7,36,8),
     ('I',7,6,7,0,42,9),
     ('J',12,9,12,0,48,10)
     ('K',12,24,8,0,57,11),
     ('L',5,16,3,0,81,12),
     ('M',7,16,7,5,81,13);

For rn (row number) = 1, x and y always stays as 0. Now I need to update the rest of x and y columns based on the above scenario(IF ..ELSE). I am unable to update the next row without updating the previous row. Is there a way to do this in a query

Answer

You can use a recursive CTE for this type of query:

WITH RECURSIVE rec(name, col, row, min_item_col, x, y, rn) AS (
    SELECT *
    FROM xyz
    WHERE rn = 1

    UNION ALL

    (
        SELECT next.name,
               next.cols,
               next.rows,
               next.minitemcols,
               CASE
                   WHEN cur.col + cur.x = 12 THEN 0
                   WHEN cur.col + cur.x = next.minitemcols THEN 0
                   /* this 3rd condition seems pointless given the last one */
                   WHEN cur.col + cur.x < 12 AND cur.x + cur.col >= next.minitemcols THEN cur.col + cur.x
                   WHEN cur.col + cur.x < 12 THEN cur.col + cur.x
               END AS x,
               CASE
                   WHEN cur.col + cur.x = 12 THEN cur.row + cur.y
                   WHEN cur.col + cur.x < 12 THEN cur.y
               END AS y,
               next.rn
        FROM rec AS cur
        JOIN xyz AS next
          ON next.rn > cur.rn
        ORDER BY rn
        LIMIT 1
    )
)
UPDATE xyz
SET x = rec.x, y = rec.y
FROM rec
WHERE xyz.rn = rec.rn
;

updates the table to

+----+----+----+-----------+----+----+--+
|name|cols|rows|minitemcols|x   |y   |rn|
+----+----+----+-----------+----+----+--+
|A   |12  |19  |8          |0   |0   |1 |
|B   |12  |11  |12         |0   |19  |2 |
|C   |5   |6   |4          |0   |30  |3 |
|D   |3   |6   |3          |5   |30  |4 |
|E   |4   |6   |3          |8   |30  |5 |
|F   |4   |6   |4          |0   |36  |6 |
|G   |3   |6   |3          |4   |36  |7 |
|H   |5   |6   |5          |7   |36  |8 |
|I   |7   |6   |7          |0   |42  |9 |
|J   |12  |9   |12         |7   |42  |10|
|K   |12  |24  |8          |NULL|NULL|11|
|L   |5   |16  |3          |NULL|NULL|12|
|M   |7   |16  |7          |NULL|NULL|13|
+----+----+----+-----------+----+----+--+

---

Note that the results are slightly different from your example from row “K” onward. I think "row J".x should be 7 not 0 (from the last else if condition), which changes the rest of the rows too. Still, I think it should be pretty easy to adapt the code to get the results you want.