From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Roy Souther <roy(at)SiliconTao(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How can I make SELECT return tuple numbers? |
Date: | 2005-07-06 03:55:48 |
Message-ID: | 20050706035548.GA1408@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-07-06 04:11:22 | Re: How can I make SELECT return tuple numbers? |
Previous Message | Roy Souther | 2005-07-06 03:34:51 | How can I make SELECT return tuple numbers? |