Re: PL/pgSQL loops? - Mailing list pgsql-sql

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