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