Thread: PL/pgSQL loops?

PL/pgSQL loops?

From
"Johnny Jørgensen"
Date:
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
 
+45 6315 7328

PL/pgSQL loops?

From
"Johnny Jørgensen"
Date:
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,

Re: PL/pgSQL loops?

From
Stephan Szabo
Date:
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';




Re: PL/pgSQL loops?

From
Tom Lane
Date:
"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


Re: PL/pgSQL loops?

From
Carl van Tast
Date:
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


Re: PL/pgSQL loops?

From
Roberto Mello
Date:
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



Re: PL/pgSQL loops?

From
"Johnny J\xF8rgensen"
Date:
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)
 
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 '
 -- [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 

            -- 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';

Re: PL/pgSQL loops?

From
Stephan Szabo
Date:
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';




Re: PL/pgSQL loops?

From
Tom Lane
Date:
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


Re: PL/pgSQL loops?

From
Roberto Mello
Date:
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



Re: PL/pgSQL loops?

From
Stephan Szabo
Date:
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.




Re: PL/pgSQL loops?

From
Tom Lane
Date:
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


Re: PL/pgSQL loops?

From
Tom Lane
Date:
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


Re: PL/pgSQL loops?

From
Stephan Szabo
Date:
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.