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