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: | Postg토토 사이트 순위SQL : Postg토토 사이트 순위SQL 메일 링리스트 : 2017-07-28 이후 PGSQL-SQL 17:48 |
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
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 |