Re: Sequences

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.