Re: [SQL] Issues with lag command

Hello

I have a test table with the following structure (2 columns: ID and time_id )and data

ID, time_id

1;"2015-01-01"
2;""
3;""
4;"2015-01-02"
5;""
6;""
7;""
8;"2015-01-03"
9;""
10;""
11;""
12;""
13;"2015-01-05"
14;""
15;""
16;""

I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)

Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on

The general logic is that anytime we find a record with a time_id null, we would like to update it with the previous time_id that is not null.

I use the LAG function and the below code



CREATE OR REPLACE FUNCTION public.update_test_dates()
  RETURNS SETOF test AS
$BODY$
DECLARE

    r test%rowtype;
BEGIN
    FOR r IN SELECT * FROM test  order by id
  
   
    LOOP
        -- can do some processing here
        if r.time_id is  null
        then
        update test set time_id= (select lag(time_id) OVER (ORDER BY id) from test where id=r.id) where id=r.id;
        end if;
       
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
  LANGUAGE plpgsql;

  select * from update_test_dates();

However, it does not work. Postgres update all rows with a NULL value

Any one can tell me what needs to be changed in my procedure in order to fix the issue?

Regards