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

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.