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

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: pgsql-general(at)postgresql(dot)org, pgpsql-bugs(at)postgresql(dot)org
Subject: Re: order by in for loop in plpgsql does not work
Date: 2002-11-26 20:45:30
Message-ID: 3DE3DD6A.9B7E04F9@nsd.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Johnson, Shaunn 2002-11-26 20:56:09 copy data into table error
Previous Message Jean-Luc Lachance 2002-11-26 20:36:02 order by in for loop in plpgsql does not work