Here are the function and the trigger :
create or replace function maj_alti_us_test()
returns trigger
LANGUAGE 'plpgsql' as
$BODY$
begin
with altisommet as (
select altisommet
from activite.geo g
where g.gid = NEW.gidgeo)
select ( case when NEW.proftoit = 0 then (select altisommet from altisommet)
ELSE lag(NEW.altitoit, 1) OVER() - NEW.proftoit END)
into new.altitoit;
return NEW;
END;
$BODY$;
create or replace trigger mytrigger
before insert or update on mytable
for each row execute function maj_alti_us_test()
An example to be inserted from a csv file:
numus proftoit profbase ...
1 0 0.35
2 0.35 0.7
3 0.7 2.3
4 2.3 2.8
5 2.8 3.7
The function maj_alti_us_test() has to fill the field altitoit (in mytable) like this:
num proftoit altitoit
5 2.80 12.75
4 2.30 15.55
3 0.70 17.85
2 0.35 18.55
1 0.00 18.90
num 2: 18.55 = 18.90 - 0.35
num 3: 17.85 = 18.55 - 0.7
...
For now, alitoit stays empty but for num 1.
Obviously, this is the wrong way to get the previous altitoit value. Do I misuse lag() ?