Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fASGOnm82140 for ; Wed, 28 Nov 2001 11:24:49 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 47599D5EC; Wed, 28 Nov 2001 08:24:54 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3D0CA5BED; Wed, 28 Nov 2001 08:24:54 -0800 (PST) Date: Wed, 28 Nov 2001 08:24:54 -0800 (PST) From: Stephan Szabo To: =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= Cc: Subject: Re: PL/pgSQL loops? In-Reply-To: <200111281540250450.0093EFC2@mail.halfahead.dk> Message-ID: <20011128081825.J32047-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id fASGY0n82507 X-Archive-Number: 200111/385 X-Sequence-Number: 5767 I'm not really sure of what's going on, and am uncertain if this is a transcription error or not. In addition table schema would be helpful for us to try the function. :) In my machine with test tables I get, NOTICE: Error occurred while executing PL/pgSQL function cleanup_order NOTICE: line 13 at for over select rows ERROR: Attribute 'cur_order_id' not found I assume this is because of the use of cur_order_id rather than cur_order.id in the inner loop definition. On Wed, 28 Nov 2001, [ISO-8859-1] "Johnny Jørgensen" wrote: > 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';