Re: Function: Change data while walking through records

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.