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
