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
I may be wrong about this, but doesn't lag need an offset value? So your statement should be `lag(time_id, 1)`?
Maybe lag defaults to offset 1 but if not, then it would seem your current statement is grabbing its own row's time_id, and you want to pull the time_id from the row offset by 1?