I have a problem with the following function:
CREATE FUNCTION cleanup_order(integer,integer)
RETURNS boolean
AS '
DECLARE
p_id ALIAS FOR $1;
o_id ALIAS FOR $2;
cur_order record;
cur_item record;
BEGIN
<< order_loop >>
FOR cur_order IN SELECT * FROM ordre WHERE person_id = p_id AND status = 1 AND id != o_id
LOOP
RAISE NOTICE ''outer: %'',cur_order.id;
<< item_loop >>
FOR cur_item IN SELECT * FROM item WHERE order_id = cur_order_id
LOOP
RAISE NOTICE ''inner: %'',cur_item.id;
UPDATE item SET ordre_id = o_id WHERE id = cur_item.id;
END LOOP;
UPDATE ordre SET status = 0 WHERE id = cur_order.id;
END LOOP;
RETURN TRUE;
END;
'
LANGUAGE 'plpgsql';
I get an error, insisting that
ERROR: parse error at or near LOOP (line 23)
- counting my way through the thing, i gather that it's the last END LOOP; that causes problems - but it's in every way similar to the inner loop?
I'm not very surefooted when it comes to plpgsql, so i'm a bit at a loss here, when pgsql says
ERROR during compile of cleanup_order near line 23
does compile mean "This is the first run, so I compile the thing" (to make it possible to create interdependent functions), or does it mean "The function is called, so I compile it, in the current setting, and execute it"?
f.x., what happens if the FOR .. IN SELECT .. LOOP doesnt get any hits at all on the SELECT?
Any suggestions about this would be greatly appreciated :)
regards,