Since your basic requirement is looking back to the prior row the lag function first comes to mind. However this dos not work as the necessary value is being calculated of the fly and lag operates on the result set (prior to lag operation). So we turn to a recursive query where at each recursion we have available the just calculated on the prior recursion. This of course assumes the other columns are already loaded. We get the desired values with:
with recursive demo( rno, r_colb, r_colc) as
( select 0, 1::numeric, 100000::numeric -- prime the recursion
union all
select sno, column_b, r_colb*r_colc
from test
join demo
on (sno = rno+1) -- next sno
)
select rno, r_colb, round(r_colc) r_colc
from demo ;
That does not get to the final need - updating the table. Fortunately, Postgres supports "update ... from (subquery) where ..." structure and the subquery just basically needs to be a valid query; including the above. So we arrive at:
update test
set column_c = r_colc
from (
with recursive demo( rno, r_colb, r_colc) as
( select 0, 1::numeric, 100000::numeric -- prime the recursion
union all
select sno, column_b, r_colb*r_colc
from test
join demo
on (sno = (select min(sno) -- since I do trust autogenerated ids
from test -- to actually be in perfect sequence
where sno > rno))
)
select rno, r_colb, round(r_colc) r_colc
from demo
) s
where sno = rno;
See demo run in v9.6.
In response to "applied for continuous calculation", yes that is possible and actually only changing the WITH clause to prime the recursion. But first, when speaking on SQL remove IF from your vocabulary - there is no such thing. There are conditionals (where, case, when...) but no IF.
The non-recursive query just gets the last known value then the recursive part looks at each subsequent and calculates column_c from it. To restart the calculation, just do the same. I modified the update query to do just that. See revised demo. It would have been possible to just modify the non-recursive query to select sno 10 (the last known value for column_c). But doing so would require updating the query for subsequent execution. Instead it looks for the last know (not null column_c) selecting that as the primer. This does require all new rows leave column_c null. But it should be good for any number of additional rows.
Note: The term "prime/primer" is my terminology for this query. It is not a general technical term used with a recursive CTE.
update test
set column_c = r_colc
from (
with recursive demo( rno, r_colb, r_colc) as
( select sno, column_b, column_c -- prime the recursion
from test
where sno = (select max(sno)
from test
where column_c is not null
)
union all
select sno, column_b, r_colb*r_colc
from test
join demo
on (sno = (select min(sno) -- since I do trust autogenerated ids
from test -- to actually be in perfect sequence
where sno > rno))
)
select rno, r_colb, round(r_colc) r_colc
from demo
) s
where sno = rno;
