Re: order by in for loop in plpgsql does not work

Hmmm...I would have said using the field numbers was the danger. I'd be
interested to hear if anyone else has experienced field names not being matched
to the correct columns.

I'd also say your problem was probably more due to how you are initialising
exchangeno and routeno variables to empty strings and then using those values
in the order by clause of the select. However, having never used that FOR
construct before I wouldn't want to swear to it not behaving as you seem to be
expecting.

--
Nigel J. Andrews

On Tue, 26 Nov 2002, Jean-Luc Lachance wrote:

> Well, I think I found why.
>
> Because OID is included in the selected fields list, the order by fields
> number are off by one.
> I rewrote the query using the field numbers instead of field names and
> the function ran as expected.
> It is a work around, but any ALTER to the table will force me to rewrite
> the field numbers.
>
> Someone should look into this. I think it is a bug.
>
> JLL
>
>
> Jean-Luc Lachance wrote:
> >
> > Any idea why when I call this function the record are not processed in
> > the order requested?
> >
> > JLL
> >
> > P.S.
> >
> > It would be nice if the syntax would allow me to write something like >>
> > cur.seqno = seq
> > and have the underlying record updated.
> >
> > declare
> >
> > cur record;
> > seq int;
> > exchangeno text;
> > routeno text;
> >
> > begin
> >
> > exchangeno := '';
> > routeno := '';
> >
> > for cur in
> > select oid, * from r order by exchangeno, routeno, street,
> > municipality, parity desc, fromno for update
> > loop
> > if cur.exchangeno != exchangeno or cur.routeno != routeno
> > then
> > seq := 1;
> > exchangeno := cur.exchangeno;
> > routeno := cur.routeno;
> > end if;
> > update r set seqno = seq, route = routeno || trim( lpad( seq, 4, '0'))
> > where oid = cur.oid;
> > seq := seq + 1;
> > end loop;
> >
> > return 0;
> > end;
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>