On Wed, May 30, 2007 at 10:03:16AM -0700, Trigve Siver wrote:
> Hi, Thanks for reply, As you have mentioned I need to get row
> numbers for my query, so when I make some other query with same
> data I will know which row number has a particular ID.
Oh, wait. If _that's_ your plan, then this will never work. The
data could change, and your row numbers would come out wrong.
What do you need "row numbers" for anyway? The very idea is inimical
to SQL, because the data is fundamentally unordered.
> As you
> mentioned "You can do this with a temporary sequence, among other
> approaches...". Can you point me to some sources or give me some
> examples, please?
BEGIN;
CREATE SEQUENCE temp_seq;
SELECT nextval('temp_seq'), other stuff from table;
DROP SEQUENCE temp_seq;
COMMIT/ROLLBACK;
If you only select, you don't have to do the DROP, you just ROLLBACK.
I think there's some nifty way to get generate_series to do this too,
but I don't know it offhand (generating row numbers sounds to me like
a bad idea, so I don't do it).
A
--
Andrew Sullivan | ajs@crankycanuck.ca