From: "Johnny Jørgensen" <johnny@halfahead.dk>
To: pgsql-sql@postgresql.org
Subject: PL/pgSQL loops?
Date: Wed, 28 Nov 2001 13:25:52 +0100
Message-ID: <200111281325520802.0018C1FB@mail.halfahead.dk> (raw)
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,
Johnny Jørgensen
johnny@halfahead.dk
+45 6315 7328
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: johnny@halfahead.dk
Subject: Re: PL/pgSQL loops?
In-Reply-To: <200111281325520802.0018C1FB@mail.halfahead.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox