Received: from smtp013.mail.yahoo.com (smtp013.mail.yahoo.com [216.136.173.57]) by postgresql.org (8.11.3/8.11.4) with SMTP id g1BHPoE45996 for ; Mon, 11 Feb 2002 12:25:50 -0500 (EST) (envelope-from chrup999@yahoo.com) Received: from chill-025.dfw.tx.bbnow.net (HELO ?192.168.0.6?) (24.219.2.25) by smtp.mail.vip.sc5.yahoo.com with SMTP; 11 Feb 2002 17:25:55 -0000 Mime-Version: 1.0 X-Sender: chrup999@pop.mail.yahoo.com Message-Id: In-Reply-To: <59358A738F45D51186A30008C74CE250E81378@slc-exc1.ctron.com> References: <59358A738F45D51186A30008C74CE250E81378@slc-exc1.ctron.com> Date: Mon, 11 Feb 2002 11:25:48 -0600 To: "Hunter, Ray" From: Chris Ruprecht Subject: Re: Sequences Cc: "'Bruno Wolff III'" , pgsql-sql@postgresql.org Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/114 X-Sequence-Number: 6542 Re: [SQL] Sequences
Ray,

You can certainly store the value in a variable, how to do that depends on the language you're using. If you're running PL/pgSQL, then you would have to define the variable somewhere on top and then use
       
var_num := ...

Best regards,
Chris

At 10:12 -0500 02/11/2002, 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@enterasys.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@wolff.to]
Sent: Monday, February 11, 2002 7:37 AM
To: Hunter, Ray
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sequences

On Mon, Feb 11, 2002 at 08:43:23AM -0500,
  "Hunter, Ray" <rhunter@enterasys.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@enterasys.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@enterasys.com' );
to add a row.


--
Chris Ruprecht
Network grunt and bit pusher extraordinaíre
_________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com