Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAT3rQm16291 for ; Wed, 28 Nov 2001 22:53:26 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id AB592D5E7; Wed, 28 Nov 2001 19:53:30 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id A10BD5BED; Wed, 28 Nov 2001 19:53:30 -0800 (PST) Date: Wed, 28 Nov 2001 19:53:30 -0800 (PST) From: Stephan Szabo To: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= Cc: Subject: Re: PL/pgSQL loops? In-Reply-To: <200111290305140952.021C953A@mail.halfahead.dk> Message-ID: <20011128194857.G37828-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/397 X-Sequence-Number: 5779 Something like the below seemed to make it work for me under 7.2b3: 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; ord record; itm record; BEGIN -- loop through existing open orders from this person, excluding the first, being the active one FOR ord 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 itm IN SELECT id FROM item WHERE ordre_id = ord.id LOOP -- relink item to latest order UPDATE item SET ordre_id = o_id WHERE id = itm.id; END LOOP; -- mark old orders as void UPDATE ordre SET status = 0 WHERE id = ord.id; END LOOP; RETURN true; END; ' LANGUAGE 'plpgsql';