Обсуждение: How can I make SELECT return tuple numbers?
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.
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.
|
Вложения
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/
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/