Обсуждение: PL/pgSQL loops?

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

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.