Is there a way to update 2 values each time calculating data on each row?

Question

I have the following table:

CREATE TABLE IF NOT EXISTS import.dre
(
    idmov integer,
    companynumber integer,
    idproduct integer,
    dtdate date,
    nrtank integer,
    dailybalance numeric,
    startbalance numeric,
    endbalance numeric
)

Populated with the following query (10 first lines only):

INSERT INTO import.dre values 
(1, 104, 10, '30/09/2023', 5, 0, NULL, 7600),
(2, 104, 10, '01/10/2023', 5,-1089.42, NULL, NULL),
(3, 104, 10, '02/10/2023', 5,-404.62, NULL, NULL),
(4, 104, 10, '03/10/2023', 5,-470.57, NULL, NULL),
(5, 104, 10, '04/10/2023', 5,-604.97, NULL, NULL),
(6, 104, 10, '05/10/2023', 5,10381.23, NULL, NULL),
(7, 104, 10, '06/10/2023', 5,-593.54, NULL, NULL),
(8, 104, 10, '07/10/2023', 5,-713.27, NULL, NULL),
(9, 104, 10, '08/10/2023', 5,-995.01, NULL, NULL),
(10, 104, 10, '09/10/2023', 5,-736.25, NULL, NULL)

I have the task to update this table follwing this pattern: 1st: startbalance must be coalesce(endbalance,0) from the last day, grouped by companynumber, idproduct, nrtank. So, using those 10 lines, at day 01/10/2023 startbalance should be 7600 and so on.

2nd: endbalance must be the startbalance of the day plus dailybalance. So, at those 10 lines, at day 01/10/2023 end balance should be 7600 + (- 1089.42) and so on.

The Final result must look something like this (forgive me if that doesn't look the most beautiful set of data you've seen today):

Select * from import.dre:

1, 104, 10, '30/09/2023', 5, 0, NULL, 7600
2, 104, 10, '01/10/2023', 5,-1089.42, 7600, 6510,58 -- 7600-1089.42
3, 104, 10, '02/10/2023', 5,-404.62, 6510,58, 6105,96 -- 6510.58-404.62
4, 104, 10, '03/10/2023', 5,-470.57, 6105.96, 5635,39
5, 104, 10, '04/10/2023', 5,-604.97, 5635,39, 5030,42
6, 104, 10, '05/10/2023', 5,10381.23, 5030,42, 15411,65
7, 104, 10, '06/10/2023', 5,-593.54, 15411,65, 14818,11
8, 104, 10, '07/10/2023', 5,-713.27, 14818,11, 14104,84
9, 104, 10, '08/10/2023', 5,-995.01, 14104,84, 13109,83
10, 104, 10, '09/10/2023', 5,-736.25, 13109,83, 12373,58

Is there some way to accomplish that? I'm trying (badly, I know) to use a for loop to update import.dre but I'm getting nowhere. If by any chance there's a better way to do it than for loop, I appreciate. I don't know how big import.dre can get.

I tried a lot of codes, but all of them updates the whole table (instead of calculating each row). The last function that i've created to debug is the following:

create or replace function teste()
returns void
language plpgsql
as
$$
declare
line record;
v_companynumber int;
v_idproduct int;
v_nrtank int;
v_dtdate date;
v_startbalance numeric;
v_endbalance numeric;
begin
for line in
    select 
        *
    from
        import.dre
    order by
        companynumber, idproduct, nrtank desc, dtdate 
    loop
        v_companynumber := line.companynumber;
        v_idproduct := line.idproduct;
        v_nrtank := line.nrtank;
        v_dtdate := line.dtdate;
        v_startbalance := lag(coalesce(line.endbalance,0), 1) over (
                partition by line.companynumber, line.idproduct, line.nrtank order by line.dtdate);
        v_endbalance := line.endbalance;
        update import.dre
            set startbalance = v_startbalance, endbalance = v_endbalance
        where
            companynumber = v_companynumber
            and idproduct = v_idproduct
            and nrtank = v_nrtank
            and dtdate = v_dtdate;
    end loop;
end;

This last query didn't assign nothing at startbalance. Tried another ones, but there is a lot of querys to put here (the best one that I got put 7600 on each day at tank 5 after but I don't remember how I got there)

Answer 1

You can do a CTE first to calculate the endbalance amd in the second steop you fill the startbalance.

WITH CTE AS (SELECT idmov, 
                    companynumber,
                    idproduct, 
                    dtdate,
                    nrtank,
                    dailybalance,
                    startbalance,
                    SUM(COALESCE(endbalance,0)+dailybalance)
                       OVER w1 AS endbalance
             FROM import.dre
             WINDOW w1 AS (PARTITION BY companynumber,idproduct,nrtank 
                           ORDER BY dtdate))
SELECT idmov, 
       companynumber,
       idproduct, 
       dtdate,
       nrtank,
       dailybalance,
       LAG(endbalance)OVER w2 AS startbalance,
       endbalance
FROM CTE
WINDOW w2 AS (PARTITION BY companynumber,idproduct,nrtank 
              ORDER BY dtdate);

idmov	companynumber	idproduct	dtdate	nrtank	dailybalance	startbalance	endbalance
1	104	10	2023-09-30	5	0	null	7600
2	104	10	2023-10-01	5	-1089.42	7600	6510.58
3	104	10	2023-10-02	5	-404.62	6510.58	6105.96
4	104	10	2023-10-03	5	-470.57	6105.96	5635.39
5	104	10	2023-10-04	5	-604.97	5635.39	5030.42
6	104	10	2023-10-05	5	10381.23	5030.42	15411.65
7	104	10	2023-10-06	5	-593.54	15411.65	14818.11
8	104	10	2023-10-07	5	-713.27	14818.11	14104.84
9	104	10	2023-10-08	5	-995.01	14104.84	13109.83
10	104	10	2023-10-09	5	-736.25	13109.83	12373.58

fiddle

Answer 2

This can be done with two window functions using a single window, without having to resort to (usually) more expensive PL/pgSQL. You didn't show what your primary key is, so I'm using a hidden ctid system column to uniquely identify each record. Demo:

with cte as (
    select ctid
          ,first_value(endbalance)over w1
           + sum(dailybalance)over w1
           - dailybalance as startbalance
          ,first_value(endbalance)over w1
           + sum(dailybalance)over w1 as endbalance
    from import.dre
    window w1 as (partition by companynumber, idproduct, nrtank
                  order by dtdate) )
update import.dre as this
set startbalance=cte.startbalance,
    endbalance  =cte.endbalance
from cte where this.ctid=cte.ctid
returning this.*;

idmov	companynumber	idproduct	dtdate	nrtank	dailybalance	startbalance	endbalance
1	104	10	2023-09-30	5	0	7600	7600
2	104	10	2023-10-01	5	-1089.42	7600.00	6510.58
3	104	10	2023-10-02	5	-404.62	6510.58	6105.96
4	104	10	2023-10-03	5	-470.57	6105.96	5635.39
5	104	10	2023-10-04	5	-604.97	5635.39	5030.42
6	104	10	2023-10-05	5	10381.23	5030.42	15411.65
7	104	10	2023-10-06	5	-593.54	15411.65	14818.11
8	104	10	2023-10-07	5	-713.27	14818.11	14104.84
9	104	10	2023-10-08	5	-995.01	14104.84	13109.83
10	104	10	2023-10-09	5	-736.25	13109.83	12373.58

Note how I cheated by not conforming to your null in the first startbalance to save a case. The demo shows how to get it back but I'd say it makes as much sense without it.

Here's the same, in the form of a re-insert.