Re: rownum

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?

--  Richard Huxton