Re: Issues with lag command

On Fri, Jul 28, 2017 at 10:48 AM, Mohamed DIA <macdia2002(at)gmail(dot)com> wrote:

> 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
>

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?

Steve