PL/pgSQL loops?

From: "Johnny Jørgensen" <johnny(at)halfahead(dot)dk>
To: pgsql-sql(at)postgresql(dot)org
Subject: PL/pgSQL loops?
Date: 2001-11-28 12:25:52
Message-ID: 200111281325520802.0018C1FB@mail.halfahead.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)halfahead(dot)dk
+45 6315 7328

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Glenn MacGregor 2001-11-28 14:07:07 View question
Previous Message Tom Lane 2001-11-28 04:47:20 Re: PL/pgSQL examples NOT involving functions