Re: sequence number in a result

"Campbell, Lance" <lance@illinois.edu> writes:
> Is there a function or special system label I can use that would
> generate a sequence number in the returning result set?

The usual hack is a temporary sequence:

regression=# create temp sequence s1;
CREATE SEQUENCE
regression=# select nextval('s1'), * from (select * from int8_tbl order by q1) ss;nextval |        q1        |
q2        
 
---------+------------------+-------------------      1 |              123 |               456      2 |
123|  4567890123456789      3 | 4567890123456789 |               123      4 | 4567890123456789 |  4567890123456789
5| 4567890123456789 | -4567890123456789
 
(5 rows)

Note that you must use a subselect to ensure that the sequence number
gets stuck on *after* the ORDER BY happens, else what you'll probably
get is numbering corresponding to the unsorted row order.

It would be possible to write a C function to do this with a lot less
overhead than a sequence entails, but no one's got round to it AFAIK.
        regards, tom lane