From johnny@halfahead.dk Wed Nov 28 12:19:54 2001 Received: from tina.andersenit.dk (tina.andersenit.dk [193.89.137.127] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id fASCJsm69644 for ; Wed, 28 Nov 2001 07:19:54 -0500 (EST) (envelope-from johnny@halfahead.dk) Received: from ([130.226.84.71]) by tina.andersenit.dk (Merak 4.00.40) with SMTP id BHB36607 for ; Wed, 28 Nov 2001 13:19:30 +0100 Message-ID: <200111281325520802.0018C1FB@mail.halfahead.dk> X-Mailer: Calypso Version 3.30.00.00 (4) Date: Wed, 28 Nov 2001 13:25:52 +0100 Reply-To: johnny@halfahead.dk From: =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= To: pgsql-sql@postgresql.org Subject: PL/pgSQL loops? Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====_10069503521989=_" X-Archive-Number: 200111/380 X-Sequence-Number: 5762 --=====_10069503521989=_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable 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 =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; 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 he= re, 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 funct= ion 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 a= ll on the SELECT? Any suggestions about this would be greatly appreciated :) regards, Johnny J=F8rgensen johnny@halfahead.dk +45 6315 7328 --=====_10069503521989=_ Content-Type: text/html; charset="us-ascii"
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

--=====_10069503521989=_-- From pgsql@halfahead.dk Wed Nov 28 14:34:06 2001 Received: from tina.andersenit.dk (tina.andersenit.dk [193.89.137.127] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id fASEY5m77805 for ; Wed, 28 Nov 2001 09:34:06 -0500 (EST) (envelope-from pgsql@halfahead.dk) Received: from ([130.226.84.71]) by tina.andersenit.dk (Merak 4.00.40) with SMTP id BHB36607 for ; Wed, 28 Nov 2001 15:34:03 +0100 Message-ID: <200111281540250450.0093EFC2@mail.halfahead.dk> References: <200111281325520802.0018C1FB@mail.halfahead.dk> X-Mailer: Calypso Version 3.30.00.00 (4) Date: Wed, 28 Nov 2001 15:40:25 +0100 From: =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= To: pgsql-sql@postgresql.org Subject: PL/pgSQL loops? Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====_100695842518039=_" X-Archive-Number: 200111/383 X-Sequence-Number: 5765 --=====_100695842518039=_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable 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 =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; 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 he= re, 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 funct= ion 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 a= ll on the SELECT? Any suggestions about this would be greatly appreciated :) regards, --=====_100695842518039=_ Content-Type: text/html; charset="us-ascii"
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,
--=====_100695842518039=_-- From sszabo@megazone23.bigpanda.com Wed Nov 28 16:24:49 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fASGOnm82140 for ; Wed, 28 Nov 2001 11:24:49 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 47599D5EC; Wed, 28 Nov 2001 08:24:54 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3D0CA5BED; Wed, 28 Nov 2001 08:24:54 -0800 (PST) Date: Wed, 28 Nov 2001 08:24:54 -0800 (PST) From: Stephan Szabo To: =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= Cc: Subject: Re: PL/pgSQL loops? In-Reply-To: <200111281540250450.0093EFC2@mail.halfahead.dk> Message-ID: <20011128081825.J32047-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id fASGY0n82507 X-Archive-Number: 200111/385 X-Sequence-Number: 5767 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'; From tgl@sss.pgh.pa.us Wed Nov 28 16:49:05 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fASGn5m83363 for ; Wed, 28 Nov 2001 11:49:05 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fASGn8k02640; Wed, 28 Nov 2001 11:49:08 -0500 (EST) To: =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= cc: pgsql-sql@postgresql.org Subject: Re: PL/pgSQL loops? In-reply-to: <200111281540250450.0093EFC2@mail.halfahead.dk> References: <200111281325520802.0018C1FB@mail.halfahead.dk> <200111281540250450.0093EFC2@mail.halfahead.dk> Comments: In-reply-to =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= message dated "Wed, 28 Nov 2001 15:40:25 +0100" Date: Wed, 28 Nov 2001 11:49:07 -0500 Message-ID: <2637.1006966147@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/386 X-Sequence-Number: 5768 =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= 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 From vanTast@Pivot.at Wed Nov 28 18:08:42 2001 Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fASI8gm86955 for ; Wed, 28 Nov 2001 13:08:42 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fASHqK886481 for pgsql-sql@postgresql.org; Wed, 28 Nov 2001 12:52:20 -0500 (EST) (envelope-from news) From: Carl van Tast X-Newsgroups: comp.databases.postgresql.sql Subject: Re: PL/pgSQL loops? Date: Wed, 28 Nov 2001 18:52:49 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 36 Message-ID: <9f8a0uccirf0f38ijrdmref7rqeo4tv6vd@4ax.com> References: <200111281540250450.0093EFC2@mail.halfahead.dk> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: scrappy@hub.org X-Newsreader: Forte Agent 1.8/32.548 To: pgsql-sql@postgresql.org X-Archive-Number: 200111/387 X-Sequence-Number: 5769 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 From rmello@cc.usu.edu Thu Nov 29 01:31:42 2001 Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.236.160]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAT1Vbm09184 for ; Wed, 28 Nov 2001 20:31:42 -0500 (EST) (envelope-from roberto@brasileiro.net) Received: from roberto by brasileiro.net with local (Exim 3.32 #1 (Debian)) id 169G3i-0005cn-00; Wed, 28 Nov 2001 18:32:14 -0700 Date: Wed, 28 Nov 2001 18:32:14 -0700 From: Roberto Mello To: Tom Lane Cc: "\"Johnny J?rgensen\"" , pgsql-sql@postgresql.org Subject: Re: PL/pgSQL loops? Message-ID: <20011128183214.A21227@cc.usu.edu> References: <200111281325520802.0018C1FB@mail.halfahead.dk> <200111281540250450.0093EFC2@mail.halfahead.dk> <2637.1006966147@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2637.1006966147@sss.pgh.pa.us> User-Agent: Mutt/1.3.23i X-Archive-Number: 200111/393 X-Sequence-Number: 5775 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!) From pgsql@halfahead.dk Thu Nov 29 01:58:50 2001 Received: from tina.andersenit.dk (tina.andersenit.dk [193.89.137.127] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAT1wnm10097 for ; Wed, 28 Nov 2001 20:58:50 -0500 (EST) (envelope-from pgsql@halfahead.dk) Received: from ([130.226.84.71]) by tina.andersenit.dk (Merak 4.10.040) with SMTP id BHB36607 for ; Thu, 29 Nov 2001 02:58:52 +0100 Message-ID: <200111290305140952.021C953A@mail.halfahead.dk> In-Reply-To: <200111281325520802.0018C1FB@mail.halfahead.dk> References: <200111281325520802.0018C1FB@mail.halfahead.dk> X-Mailer: Calypso Version 3.30.00.00 (4) Date: Thu, 29 Nov 2001 03:05:14 +0100 From: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= To: pgsql-sql@postgresql.org Subject: Re: PL/pgSQL loops? Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====_100699951427590=_" X-Archive-Number: 200111/395 X-Sequence-Number: 5777 --=====_100699951427590=_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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), bel= ow 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 (sin= ce \g [file] adds some space above the function), but the only line reasona= bly 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 s= emicolon should be there, so what gives? Actually, the two loops should not be neccessary (after each interrupted se= ssion, 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 thi= s, regards, Johnny J=F8rgensen 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 or= der 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 firs= t, being the active one FOR cur_order_id IN SELECT id FROM ordre WHERE person_id =3D p_id AND sta= tus =3D 1 AND id !=3D o_id LOOP -- loop through items linked to the currently looped order FOR cur_item_id IN SELECT id FROM item WHERE order_id =3D cur_order_= id LOOP -- relink item to latest order UPDATE item SET ordre_id =3D o_id WHERE id =3D cur_item_id; END LOOP; -- mark old orders as void UPDATE ordre SET status =3D 0 WHERE id =3D cur_order_id; END LOOP; RETURN true; END; ' LANGUAGE 'plpgsql'; --=====_100699951427590=_ Content-Type: text/html; charset="us-ascii"
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';
--=====_100699951427590=_-- From sszabo@megazone23.bigpanda.com Thu Nov 29 03:53:26 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAT3rQm16291 for ; Wed, 28 Nov 2001 22:53:26 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id AB592D5E7; Wed, 28 Nov 2001 19:53:30 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id A10BD5BED; Wed, 28 Nov 2001 19:53:30 -0800 (PST) Date: Wed, 28 Nov 2001 19:53:30 -0800 (PST) From: Stephan Szabo To: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= Cc: Subject: Re: PL/pgSQL loops? In-Reply-To: <200111290305140952.021C953A@mail.halfahead.dk> Message-ID: <20011128194857.G37828-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/397 X-Sequence-Number: 5779 Something like the below seemed to make it work for me under 7.2b3: 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; ord record; itm record; BEGIN -- loop through existing open orders from this person, excluding the first, being the active one FOR ord 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 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'; From tgl@sss.pgh.pa.us Thu Nov 29 06:09:04 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAT694m20548 for ; Thu, 29 Nov 2001 01:09:04 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fAT68ak15772; Thu, 29 Nov 2001 01:08:37 -0500 (EST) To: Stephan Szabo cc: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= , pgsql-sql@postgresql.org Subject: Re: PL/pgSQL loops? In-reply-to: <20011128194857.G37828-100000@megazone23.bigpanda.com> References: <20011128194857.G37828-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Wed, 28 Nov 2001 19:53:30 -0800" Date: Thu, 29 Nov 2001 01:08:36 -0500 Message-ID: <15769.1007014116@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/400 X-Sequence-Number: 5782 Stephan Szabo 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 From sszabo@megazone23.bigpanda.com Thu Nov 29 16:09:44 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fATG9hm75410 for ; Thu, 29 Nov 2001 11:09:44 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 553D6D5EB; Thu, 29 Nov 2001 08:09:48 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 4AF745BED; Thu, 29 Nov 2001 08:09:48 -0800 (PST) Date: Thu, 29 Nov 2001 08:09:48 -0800 (PST) From: Stephan Szabo To: Tom Lane Cc: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= , Subject: Re: PL/pgSQL loops? In-Reply-To: <15769.1007014116@sss.pgh.pa.us> Message-ID: <20011129073009.E43179-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/412 X-Sequence-Number: 5794 On Thu, 29 Nov 2001, Tom Lane wrote: > Stephan Szabo 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 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. From tgl@sss.pgh.pa.us Thu Nov 29 17:05:24 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fATH5Om77662 for ; Thu, 29 Nov 2001 12:05:24 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fATH2tk19093; Thu, 29 Nov 2001 12:02:55 -0500 (EST) To: Stephan Szabo cc: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= , pgsql-sql@postgresql.org Subject: Re: PL/pgSQL loops? In-reply-to: <20011129073009.E43179-100000@megazone23.bigpanda.com> References: <20011129073009.E43179-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 29 Nov 2001 08:09:48 -0800" Date: Thu, 29 Nov 2001 12:02:55 -0500 Message-ID: <19090.1007053375@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/413 X-Sequence-Number: 5795 Stephan Szabo 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 From tgl@sss.pgh.pa.us Thu Nov 29 23:20:01 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fATNK1m36586 for ; Thu, 29 Nov 2001 18:20:01 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fATND5k03777; Thu, 29 Nov 2001 18:13:05 -0500 (EST) To: Stephan Szabo cc: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= , pgsql-sql@postgresql.org Subject: Re: PL/pgSQL loops? In-reply-to: <19090.1007053375@sss.pgh.pa.us> References: <20011129073009.E43179-100000@megazone23.bigpanda.com> <19090.1007053375@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Thu, 29 Nov 2001 12:02:55 -0500" Date: Thu, 29 Nov 2001 18:13:05 -0500 Message-ID: <3774.1007075585@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/423 X-Sequence-Number: 5805 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 From sszabo@megazone23.bigpanda.com Fri Nov 30 09:29:26 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAU9TQm74228 for ; Fri, 30 Nov 2001 04:29:26 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3209DD5F1; Fri, 30 Nov 2001 01:29:27 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 23B0E5BED; Fri, 30 Nov 2001 01:29:27 -0800 (PST) Date: Fri, 30 Nov 2001 01:29:27 -0800 (PST) From: Stephan Szabo To: Tom Lane Cc: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= , Subject: Re: PL/pgSQL loops? In-Reply-To: <3774.1007075585@sss.pgh.pa.us> Message-ID: <20011130012817.T52306-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/433 X-Sequence-Number: 5815 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.