Thread: Function: Change data while walking through records
Hi, I keep having this need to create a function that will change the row data as I am walking through the data. For example, I process each row in order, if column1 change from previous row, set column2 to true. Is this possible? I can run another query to modify the data, but that doesn't seem that efficient? Thanks for any help. -- An example: --Simple table create table tmp1 ( id integer, time timestamp, sequential boolean); insert into tmp1 values (1, '2008-01-01 12:00:00', false),(2, '2008-01-02 12:00:00', false),(3, '2008-01-03 12:00:00', false),(4, '2008-01-08 12:00:00', false),(5, '2008-01-09 12:00:00', false),(6, '2008-01-10 12:00:00', false),(7, '2008-01-15 12:00:00', false),(8, '2008-01-22 12:00:00', false),(9, '2008-01-23 12:00:00', false),(10, '2008-01-25 12:00:00', false); -- I want line: "my_sequential := true;" to effect that table CREATE OR REPLACE FUNCTION setseq() RETURNS INTEGER AS $BODY$ DECLARE my_id integer; my_time integer; my_sequential boolean; old_time integer; change_count integer; BEGIN change_count := 1; for my_id,my_time,my_sequential in select id,extract(day from time),sequential from tmp1 order by time LOOP if (old_time is not null) then if (old_time+1 = my_time) then my_sequential := true; --How do I make this work? change_count := change_count+1; end if; end if; old_time := my_time; END LOOP; return change_count; END; $BODY$ LANGUAGE 'plpgsql'; -- View this message in context: http://www.nabble.com/Function%3A-Change-data-while-walking-through-records-tp23873836p23873836.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
stevesub wrote: > I keep having this need to create a function that will change the row data > as I am walking through the data. For example, I process each row in order, > if column1 change from previous row, set column2 to true. > > Is this possible? I can run another query to modify the data, but that > doesn't seem that efficient? > > Thanks for any help. > > -- > > An example: > > --Simple table > create table tmp1 ( > id integer, > time timestamp, > sequential boolean); > > insert into tmp1 values (1, '2008-01-01 12:00:00', false),(2, > '2008-01-02 > 12:00:00', false),(3, '2008-01-03 12:00:00', false),(4, '2008-01-08 > 12:00:00', false),(5, '2008-01-09 12:00:00', false),(6, '2008-01-10 > 12:00:00', false),(7, '2008-01-15 12:00:00', false),(8, '2008-01-22 > 12:00:00', false),(9, '2008-01-23 12:00:00', false),(10, '2008-01-25 > 12:00:00', false); > > -- I want line: "my_sequential := true;" to effect that table > CREATE OR REPLACE FUNCTION setseq() RETURNS INTEGER AS > $BODY$ > DECLARE > my_id integer; > my_time integer; > my_sequential boolean; > old_time integer; > change_count integer; > BEGIN > change_count := 1; > > for my_id,my_time,my_sequential in > select id,extract(day from time),sequential from tmp1 order by time > LOOP > if (old_time is not null) then > if (old_time+1 = my_time) then > my_sequential := true; --How do I make this work? > change_count := change_count+1; > end if; > end if; > old_time := my_time; > END LOOP; > return change_count; > END; > $BODY$ > LANGUAGE 'plpgsql'; You could use a cursor and UPDATE ... WHERE CURRENT OF for that. Here are some lines of code: DECLARE ... mycur CURSOR FOR select id,extract(day from time),sequential from tmp1 order by time FOR UPDATE; row tmp1%ROWTYPE; BEGIN ... OPEN mycur; LOOP FETCH mycur INTO row; EXIT WHEN NOT FOUND; ... UPDATE tmp1 SET sequential = TRUE WHERE CURRENT OF mycur; ... END LOOP; CLOSE mycur; ... END; Yours, Laurenz Albe
Albe Laurenz *EXTERN* wrote: > > stevesub wrote: >> I keep having this need to create a function that will change the row >> data >> as I am walking through the data. For example, I process each row in >> order, >> if column1 change from previous row, set column2 to true. >> >> Is this possible? I can run another query to modify the data, but that >> doesn't seem that efficient? >> >> Thanks for any help. >> >> -- >> >> An example: >> >> --Simple table >> create table tmp1 ( >> id integer, >> time timestamp, >> sequential boolean); >> >> insert into tmp1 values (1, '2008-01-01 12:00:00', false),(2, >> '2008-01-02 >> 12:00:00', false),(3, '2008-01-03 12:00:00', false),(4, '2008-01-08 >> 12:00:00', false),(5, '2008-01-09 12:00:00', false),(6, '2008-01-10 >> 12:00:00', false),(7, '2008-01-15 12:00:00', false),(8, '2008-01-22 >> 12:00:00', false),(9, '2008-01-23 12:00:00', false),(10, '2008-01-25 >> 12:00:00', false); >> >> -- I want line: "my_sequential := true;" to effect that table >> CREATE OR REPLACE FUNCTION setseq() RETURNS INTEGER AS >> $BODY$ >> DECLARE >> my_id integer; >> my_time integer; >> my_sequential boolean; >> old_time integer; >> change_count integer; >> BEGIN >> change_count := 1; >> >> for my_id,my_time,my_sequential in >> select id,extract(day from time),sequential from tmp1 order by time >> LOOP >> if (old_time is not null) then >> if (old_time+1 = my_time) then >> my_sequential := true; --How do I make this work? >> change_count := change_count+1; >> end if; >> end if; >> old_time := my_time; >> END LOOP; >> return change_count; >> END; >> $BODY$ >> LANGUAGE 'plpgsql'; > > You could use a cursor and UPDATE ... WHERE CURRENT OF for that. > > Here are some lines of code: > > DECLARE > ... > mycur CURSOR FOR select id,extract(day from time),sequential from tmp1 > order by time FOR UPDATE; > row tmp1%ROWTYPE; > BEGIN > ... > OPEN mycur; > LOOP > FETCH mycur INTO row; > EXIT WHEN NOT FOUND; > ... > UPDATE tmp1 SET sequential = TRUE WHERE CURRENT OF mycur; > ... > END LOOP; > CLOSE mycur; > ... > END; > > Yours, > Laurenz Albe > > -- > > Thanks. Follow up question: It appears that I cannot modify data using the cursor when there is an 'order by' clause (I get: 'ERROR: cursor "mycur" is not a simply updatable scan of table "tmp1"'). Is there anyway to get around this, via an index or something? Thanks. -- View this message in context: http://www.nabble.com/Function%3A-Change-data-while-walking-through-records-tp23873836p23985514.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.