Re: Function: Change data while walking through records

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