Does continue in a loop not commit any changes

From: "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Does continue in a loop not commit any changes
Date: 2012-08-24 20:44:08
Message-ID: CACg0vTn1Q9xp=PGCbR-uE8XBTwM5T2TTXKq3qhyS+HMU3fKsCQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings all,

Having an issue with the pl/pgsql function below. I may or may not
make an update to a table in the first IF statement. However, whenever
an update is made, if the loop is continued then the update is not
committed.
Is it that whenever a loop is not completed rows aren't written?
Didn't see anything in the docs that would suggest that so I think
that I am missing something very obvious.

Thanks,

Rhys

CREATE OR REPLACE FUNCTION netone.flow_(origin integer)
RETURNS void AS
$BODY$

DECLARE
lr record;
nextid integer;
nextgeom geometry;
l2 record;

BEGIN
create temporary table if not exists pointhold(id integer) on commit drop;
create temporary table if not exists linehold(id integer) on commit drop;
RAISE NOTICE 'STARTING AT %', origin;
for lr in SELECT gnid, id,a.geom as ag, b.geom as bg from
netone.points a, netone.lines b where gnid = origin AND
st_intersects(a.geom,b.geom) /*AND id NOT IN (select id from
linehold)*/ LOOP
RAISE NOTICE 'LINE # %', lr.id;
IF st_intersects(st_startpoint(lr.bg),lr.ag) THEN /**** statement
where table may or may no be updated****/
raise notice 'reversed';
update netone.lines set geom = st_reverse(geom) where id = lr.id;
END IF;
insert into pointhold (id) values (origin);
insert into linehold (id) values (lr.id);
select into l2 gnid, status from netone.points where
st_intersects(lr.bg, geom) AND gnid not in (select id from pointhold);
CONTINUE WHEN l2.status = 'OPENED' /*** once this is true the
previous update is not committed ***/

PERFORM netone.flow_(l2.gnid);
END LOOP;
END;

$BODY$
LANGUAGE plpgsql

Browse pgsql-general by date

  From Date Subject
Next Message javad M 2012-08-24 21:24:48 Re: Cannot Run EnterpriseDB Postgresql Installation
Previous Message Kevin Grittner 2012-08-24 20:29:52 Re: run function on server restart