"Campbell, Lance" <[EMAIL PROTECTED]> 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
