How to use lag() window in a PL/pgsql trigger

62 Views Asked by At

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() ?

0

There are 0 best solutions below