Get Column value of previous row in postgres "cannot use window function in UPDATE"

Question

When i is greater than or equal to 3, I am trying to fetch the previous row value of that column and trying to use it in the current row calculation and I have tried using Lag function to do so but was not successful,was getting the error as "cannot use window function in UPDATE". Could some one help me out.Thanks!

CREATE OR REPLACE FUNCTION vin_calc() RETURNS text AS
$BODY$
DECLARE
    r res%rowtype;
    i integer default 0;
    x  text;
    curs2 CURSOR FOR SELECT * FROM res;
BEGIN
open curs2;
   -- FOR r IN curs2 
  LOOP
  FETCH curs2 INTO r;
  exit when not found;

    if(x!=r.prod_grp_nm) then
    i:=0;
    end if;

     i:= i+1;

     if (i=1) then
      update res set duration =0 where 
      dur=r.dur and prod_grp_nm=r.prod_grp_nm and week_end=r.week_end;


     elsif(i=2) then
     update res set duration =1 where 
      dur=r.dur and prod_grp_nm=r.prod_grp_nm and week_end=r.week_end;


     elsif(i>=3) then
     update res set gwma_duration =0.875*lag(res.duration,1) over()+(0.125*r.dur) where 
      dur=r.dur and prod_grp_nm=r.prod_grp_nm and week_end=r.week_end;
     end if ;

    x:=r.prod_grp_nm;      

    END LOOP;
    RETURN 'yes';
END
$BODY$
LANGUAGE 'plpgsql' ;

Answer 1

Assuming that ...

• gwma_duration and duration are supposed to be the same column and differ due to typos.

• You want to order by a column named order_column. Replace with your actual column(s).

• Your primary key columns is res_id. Replace with your actual column(s).

Put some lipstick on a pig:

Your procedural code repared and improved:

CREATE OR REPLACE FUNCTION vin_calc()
  RETURNS void AS
$func$
DECLARE
   r res%rowtype;
   i integer := 0;
   last_grp text;
BEGIN

FOR r IN
   SELECT * FROM res
LOOP
   IF last_grp <> r.prod_grp_nm THEN
      i := 1;
   ELSE
      i := i + 1;
   END IF;

   IF i < 3 THEN
      UPDATE res
      SET    duration = i - 1
      WHERE  dur = r.dur
      AND    prod_grp_nm = r.prod_grp_nm
      AND    week_end = r.week_end;

   ELSE
      UPDATE res r1
      SET    duration = r.dur * 0.125 + 
            (SELECT 0.875 * gwma_duration FROM res
             WHERE order_column < r1.order_column
             ORDER BY order_column
             LIMIT 1
            )  -- could be replaced with last_duration, analog to last_grp
      WHERE  r1.dur = r.dur
      AND    r1.prod_grp_nm = r.prod_grp_nm
      AND    r1.week_end = r.week_end;
   END IF;

   last_grp := r.prod_grp_nm;

   END LOOP;
END
$func$
LANGUAGE plpgsql;

• Use the implicit cursor of a FOR loop. No need for unwieldy explicit cursor.

• Never quote the language name plpgsql, which is an identifier, not a string.

• Simplified your logic in several places.

• Most importantly, as the error message tells you, you can't use window functions in a SET clause of an UPDATE. I replaced it with a correlated subquery. But could probably be replaced with with last_duration, analog to last_grp: just remember the value from the last iteration.

Proper solution

However, all of the above is very inefficient when you can do it in a single UPDATE statement:

UPDATE res r
SET    duration = CASE WHEN r0.rn < 3
                     THEN r0.rn - 1
                     ELSE r0.last_dur * 0.875 + r.dur * 0.125
                  END
FROM  (
   SELECT res_id, duration
        , row_number()  OVER (PARTITION BY prod_grp_nm ORDER BY order_column) AS rn
        , lag(duration) OVER (PARTITION BY prod_grp_nm ORDER BY order_column) AS last_dur
   FROM res
   ) r0
WHERE  r.res_id = r0.res_id

• To be clear: you can use window functions in the FROM clause - at least in modern-day versions of Postgres.

• Use row_number(), not rank() to be equivalent to your procedural code.