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

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)