Thread: How can I make SELECT return tuple numbers?

How can I make SELECT return tuple numbers?

From
Roy Souther
Date:
Is there any way to make a SELECT return an extra field that is a sequential number for the tuples that are returned?
I am trying to populate a table with an INSERT SELECT and would like to make one of the fields a sequence starting with 1 for the first tuple and incromenting. I don't want the field to be a unique index. As the query is run again and again I want more tuples appended to the table and each time the counter should start at 1 and count up.

Roy Souther
www.SiliconTao.com
Let Open Source help your business move beyond.

For security this message is digitally authenticated by GnuPG.



Attachment

Re: How can I make SELECT return tuple numbers?

From
Michael Fuhr
Date:
On Tue, Jul 05, 2005 at 09:34:51PM -0600, Roy Souther wrote:
>
> Is there any way to make a SELECT return an extra field that is a
> sequential number for the tuples that are returned?

You could use a sequence.

> I am trying to populate a table with an INSERT SELECT and would like to
> make one of the fields a sequence starting with 1 for the first tuple
> and incromenting. I don't want the field to be a unique index. As the
> query is run again and again I want more tuples appended to the table
> and each time the counter should start at 1 and count up.

You could reset a sequence's start value before each insert.  You
might even be able to it automagically with a statement-level before
trigger that resets the sequence value and a DEFAULT expression or
row-level trigger that uses nextval() to obtain the next sequence
value.  That appears to work in simple tests, although I admittedly
haven't thought it through very far to see if there would be any
problems.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: How can I make SELECT return tuple numbers?

From
Michael Fuhr
Date:
On Tue, Jul 05, 2005 at 09:55:48PM -0600, Michael Fuhr wrote:
>
> You could reset a sequence's start value before each insert.  You
> might even be able to it automagically with a statement-level before
> trigger that resets the sequence value and a DEFAULT expression or
> row-level trigger that uses nextval() to obtain the next sequence
> value.  That appears to work in simple tests, although I admittedly
> haven't thought it through very far to see if there would be any
> problems.

One problem would involve concurrent inserts obtaining values from
the same sequence; I think using a temporary (i.e., session-local)
sequence should mitigate that problem.  You could create the sequence
in a statement-level before trigger and drop it in a statement-level
after trigger to make its use somewhat invisible.  But again, I've
only done this in simple tests, so it deserves more thought.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/