From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Gregory Stark <gsstark(at)mit(dot)edu> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: not exactly a bug report, but surprising behaviour |
Date: | 2003-02-04 19:56:21 |
Message-ID: | 20030204114257.F7488-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 4 Feb 2003, Gregory Stark wrote:
> It seems the sort operation is done very late in the process, after functions
> in the select column list have already been called and the results stored.
> This makes using sequences to generate a sequential list of numbers have a
> surprising behaviour.
Select list entries are done before order by since you can order by the
output of a select list entry.
> slo=> create sequence w;
> CREATE SEQUENCE
> slo=> create table w1 (w integer);
> CREATE TABLE
> slo=> insert into w1 values (1);
> INSERT 229135376 1
> slo=> insert into w1 values (2);
> INSERT 229135377 1
> slo=> insert into w1 values (3);
> INSERT 229135378 1
> slo=> create table w2 as (select nextval('w'),w from w1 order by w desc);
> SELECT
> slo=> select * from w2;
> nextval | w
> ---------+---
> 3 | 3
> 2 | 2
> 1 | 1
> (3 rows)
I think the query that would give you what you want in this case is
something like the following to force the order before doing the nextvals:
create table w2 as select nextval('w'), w from (select w from w1 order by
w desc) w1;
However, I'm not sure that you can count on that working in all cases
either.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2003-02-04 19:58:01 | Re: now() more precise than the transaction |
Previous Message | Bruno Wolff III | 2003-02-04 19:54:39 | Re: not exactly a bug report, but surprising behaviour |