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
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 |