Re: sequence number in a result

"Campbell, Lance" <lance(at)illinois(dot)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