"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
