Re: Sequences

hi hunter,

nextval increments the sequence in an atomic operation and returns the
current value
of the sequence. It is transaction safe in the sense,

if two webserver process do two transactions simultaneously they would
still get different
current vals.

so last_val should not be the approach.

forgive me if i am not understanding the intricacies of your
problem.

"Hunter, Ray" wrote:

>
>
> The only problem with this solution is that I have already incremented
> the sequence and need to use the current value? Is there a way to
> assign the value to a variable and then use it in an insert statement?
>
> This is just a hashed out example. I am hoping it can all be done in
> sql statements...
> Example:
> var num = select last_value from user_table_id_seq;
>
> insert into users ( "user_id", "user_fname", "user_lname",
> "user_email" )
> values ( 'num', 'Ray', 'Hunter', 'rhunter(at)enterasys(dot)com' );
>
> If I could do this is would be great...Is it possible?
>
> Thanks,
>
>
>
> Ray Hunter
> Firmware Engineer
>
> ENTERASYS NETWORKS
>
> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno(at)wolff(dot)to]
> Sent: Monday, February 11, 2002 7:37 AM
> To: Hunter, Ray
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] Sequences
>
> On Mon, Feb 11, 2002 at 08:43:23AM -0500,
> "Hunter, Ray" <rhunter(at)enterasys(dot)com> wrote:
> > I have various sequences in my database set up for ids. My question
>
> > is: How can I get the current value of the sequence without creating
> a
> > session and using the currval function?
> >
> > I would like to take the current value of the sequence and use it
> for
> > a value in an insert statement.
> >
> >
> > Example:
> >
> > insert into users ( "user_id", "user_fname", "user_lname",
> > "user_email" )
> > values ( 'current sequence', 'Ray', 'Hunter',
> 'rhunter(at)enterasys(dot)com'
> > );
>
> I think you want to use nextval in this context. currval is used when
> you have already gotten a new sequence number and want to use it in
> several inserts in the same transaction.
>
> If you make user_id a serial type, then its default value will be
> nextval and you could use: insert into users ( "user_fname",
> "user_lname", "user_email" )
>
> values ('Ray', 'Hunter', 'rhunter(at)enterasys(dot)com' );
> to add a row.