Issues with lag command

From: Mohamed DIA <macdia2002(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Issues with lag command
Date: 2017-07-28 17:48:53
Message-ID: CA+oNSn9i4PCRQKvwpGM3wRkWfMMSW6Mrc-9KsUdhWQceQFjcpA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Steve Midgley 2017-07-28 19:21:49 Re: Issues with lag command
Previous Message Scott Marlowe 2017-07-25 21:07:37 Re: How to duplicate postgres 9.4 database