Thread: PL/pgSQL loops?
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;
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;
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';
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
+45 6315 7328
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;
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;
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';
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,
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';
"Johnny Jørgensen" <pgsql@halfahead.dk> writes: > I have a problem with the following function: > [snip] > - 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 can't see anything wrong with it either. What PG version are you running? > 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 funct= > ion is called, so I compile it, in the current setting, and execute it"? I've tried to clarify this in the docs for 7.2; see http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql.html#PLPGSQL-OVERVIEW regards, tom lane
On Wed, 28 Nov 2001 14:49:42 +0000 (UTC), pgsql@halfahead.dk ("Johnny
Jørgensen") wrote:
> << order_loop >>
> FOR cur_order IN SELECT * FROM ordre WHERE person_id =3D p_id AND status =
>=3D 1 AND id !=3D o_id
> LOOP
> RAISE NOTICE ''outer: %'',cur_order.id;
>
> << item_loop >>
> FOR cur_item IN SELECT * FROM item WHERE order_id =3D cur_order_id
> LOOP
> RAISE NOTICE ''inner: %'',cur_item.id;
>
> UPDATE item SET ordre_id =3D o_id WHERE id =3D cur_item.id;
>
> END LOOP;
>
> UPDATE ordre SET status =3D 0 WHERE id =3D cur_order.id;
>
> END LOOP;
Don't know much about LOOPs. But wouldn't this do want you want?
UPDATE item SET ordre_id = o_id FROM ordre WHERE ordre.id = item.ordre_id AND ordre.person_id = p_id AND ordre.status
=1 AND ordre.id != o_id
UPDATE ordre SET status = 0 WHERE person_id = p_id AND status = 1 AND id != o_id
Kind regards,Carl van Tast
On Wed, Nov 28, 2001 at 11:49:07AM -0500, Tom Lane wrote: > > I've tried to clarify this in the docs for 7.2; see > http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql.html#PLPGSQL-OVERVIEW Nice work Tom (as usual). It made things quite a bit clearer. Thanks. -Roberto -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer TAGLINE ESTEPE (a original furou!) ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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)
-------------------------------------------------------------
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 J�rgensen
function definition below
-------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION cleanup_order(integer,integer)
RETURNS boolean
AS '
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
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';
Something like the below seemed to make it work for me under
7.2b3:
CREATE FUNCTION cleanup_order(integer,integer)RETURNS booleanAS ' -- [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
idFROM ordre WHERE person_id = p_id AND status = 1 AND id != o_id LOOP -- loop through items linked to the
currentlylooped 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';
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Something like the below seemed to make it work for me under
> 7.2b3:
Oh ... duh! I've been bit by that before myself. In the original,
since the FOR loops were using integer variables (not record or rowtype
variables as they should've), the plpgsql parser was expecting an
integer FOR-loop. Which goes like
FOR ivar IN expression .. expression LOOP stmts END LOOP;
Evidently, somewhere around the END LOOP it realized that it was missing
the .. part of the construct.
I've mumbled before that it's bad form to be using the declared type
of the FOR variable to drive the syntaxing of FOR loops --- poor error
recovery and unhelpful error messages are exactly the reasons why.
Not sure that this can easily be fixed, however.
regards, tom lane
On Wed, Nov 28, 2001 at 11:49:07AM -0500, Tom Lane wrote: > > I've tried to clarify this in the docs for 7.2; see > http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql.html#PLPGSQL-OVERVIEW Nice work Tom (as usual). It made things quite a bit clearer. Thanks. -Roberto -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer TAGLINE ESTEPE (a original furou!) ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Thu, 29 Nov 2001, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > Something like the below seemed to make it work for me under > > 7.2b3: > > Oh ... duh! I've been bit by that before myself. In the original, > since the FOR loops were using integer variables (not record or rowtype > variables as they should've), the plpgsql parser was expecting an > integer FOR-loop. Which goes like > > FOR ivar IN expression .. expression LOOP stmts END LOOP; > > Evidently, somewhere around the END LOOP it realized that it was missing > the .. part of the construct. > > I've mumbled before that it's bad form to be using the declared type > of the FOR variable to drive the syntaxing of FOR loops --- poor error > recovery and unhelpful error messages are exactly the reasons why. > Not sure that this can easily be fixed, however. That is wierd. I think I may see what's happening though. ISTM, "fori_lower" in plpgsql's gram.y keeps going until it finds a .. or semicolon. Then the next expression keeps going until it finds LOOP or a semicolon. In the original example this means the outer's FOR expressions seem to be (with some help from elog(notice)) the select id through the semicolon on the first update and then end loop as the second. At which point it seems to bomb on the semicolon. In addition, this seems to work: for intvar in 1 .. (select max(a) from a) LOOP and this doesn't: for intvar in 1 .. (select max(loop) from a) LOOP and this does: for intvar in (select max(loop) from a) .. 1 LOOP I'm guessing that plpgsql doesn't want to try to determine if an expression is valid and so just concats stuff to pass off, but that seems to mean that you need to double quote loop in the second (which is somewhat unobvious). You don't need to in the third because fori_lower doesn't stop when it sees a LOOP. A quick hack for the case where you have a FOR intvar IN <single expression> LOOP cases where you meant FOR record IN may be to make fori_lower stop when it sees a LOOP and error (there's an equivalent error for running off the end of the function) at which point we can probably give a meaningful error message.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> In addition, this seems to work:
> for intvar in 1 .. (select max(a) from a) LOOP
> and this doesn't:
> for intvar in 1 .. (select max(loop) from a) LOOP
> and this does:
> for intvar in (select max(loop) from a) .. 1 LOOP
> I'm guessing that plpgsql doesn't want to try to determine if
> an expression is valid and so just concats stuff to pass off,
Correct ...
> but that seems to mean that you need to double quote loop in
> the second (which is somewhat unobvious).
I would've expected it to count nested parentheses, at least.
A quick look makes it appear that some places in plpgsql's
gram.y do, and some don't. Ugh.
regards, tom lane
I said:
> I would've expected it to count nested parentheses, at least.
Okay, it does that now, and I found that I could tighten the error
checking to give a sooner/more useful error. Now, if you get caught
by the integer-FOR-vs-FOR-over-rows problem, you'll probably get
ERROR: missing .. at end of SQL expression
which at least gives you some clue that the problem has something to
do with the "FOR x IN foo .. bar" syntax.
regards, tom lane
On Thu, 29 Nov 2001, Tom Lane wrote: > I said: > > I would've expected it to count nested parentheses, at least. > > Okay, it does that now, and I found that I could tighten the error > checking to give a sooner/more useful error. Now, if you get caught > by the integer-FOR-vs-FOR-over-rows problem, you'll probably get > > ERROR: missing .. at end of SQL expression > > which at least gives you some clue that the problem has something to > do with the "FOR x IN foo .. bar" syntax. Definately better, and it probably gives a more meaningful line number too.