Re: PL/pgSQL loops?

Ok, I'll try to clarify a bit:
first the version,

SELECT version();
-->

version
-------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

now,

The cur_order_id <- cur_order.id was because it was snipped midways through an attempt, looping through records instead (which didnt work either), below is the original function.

As for the schema, it looks like this:

TABLE ordre (
[PK] id int4,
[FK] person_id int4,
placement_date timestamp,
status int2
)

TABLE item (
[PK] id int4,
[FK] ordre_id int4,
[FK] uniq_id int4,
price decimal(10,2)
)

as for the errors, i double checked, with this very scheme, and oddly, the error message changes. I have a hard time actually numbering the lines (since \g [file] adds some space above the function), but the only line reasonably close is the *inner*

END LOOP;

- not the outer anymore? The error this time is:

Parse error at or near ";"

- I double checked my books, and the docs, and as far as I can tell, the semicolon should be there, so what gives?
Actually, the two loops should not be neccessary (after each interrupted session, a cleanup will be made, so there'll be only one stranded order at a time),
but I dont like the idea of eventual hits being "silently discarded"..

- hope this clarifies the issue a bit, and that someone can see through this,

regards,

Johnny Jrgensen

function definition below
-------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION cleanup_order(integer,integer)
RETURNS boolean
AS '
-- [INT order_id] cleanup_order ( INT person_id, INT order_id )
-- purpose: when a session is interrupted, the order is left open, when the next session is initiated,
-- transfer the items to a new order, and mark the older order void ( so we can tell if f.x. people abort
-- an order in the payment phase, e.g. indicating they dont like our payment options )

DECLARE
p_id ALIAS FOR $1;
o_id ALIAS FOR $2;
cur_order_id integer;
cur_item_id integer;

BEGIN

-- loop through existing open orders from this person, excluding the first, being the active one
FOR cur_order_id IN SELECT id FROM ordre WHERE person_id = p_id AND status = 1 AND id != o_id
LOOP
-- loop through items linked to the currently looped order
FOR cur_item_id IN SELECT id FROM item WHERE order_id = cur_order_id
LOOP

-- relink item to latest order
UPDATE item SET ordre_id = o_id WHERE id = cur_item_id;

END LOOP;

-- mark old orders as void
UPDATE ordre SET status = 0 WHERE id = cur_order_id;

END LOOP;

RETURN true;

END;
'
LANGUAGE 'plpgsql';