Re: How can I make SELECT return tuple numbers?

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/