Re: rownum

Richard Huxton wrote:
> On Friday 14 Feb 2003 5:20 am, Tom Lane wrote:
> 
>>    select nextval('rownum'), * from (select ... order by ...) sub;
>>
>>The overhead of using a sequence for this is pretty annoying.  It would
>>be a simple matter to write a C function that emits sequential values
>>without any database access (see pg_stat_get_backend_idset() for some
>>inspiration).  But you'd still need the subselect to avoid getting
>>re-sorted.  AFAICS any rownum() function that doesn't behave like that
>>is a flat violation of the SQL standard...
> 
> 
> Could you not build a wrapper function something like:
> 
> SELECT with_rownum('SELECT ...');
> 
> Where the function returns SETOF RECORD or similar - just fetch rows from the 
> select and prepend a pg_rownum column?
> 

I've played with this a bit in the past, and concluded that the best way 
to do it (if in fact you agree it should be done at all), would be to 
add a rownum pseudo column as the results are projected from the backend 
to the frontend. I think this would require a change to the FE/BE 
protocol, which we've talked about doing for 7.4.

Joe