Thread

1. simulating INSERT return values with default values of sequences

   Thomas F.O'Connell <tfo@monsterlabs.com> — 2002-07-22T19:18:54Z

   i didn't see this question asked (or answered in the mailing lists), but 
   it seems like it would be an FAQ...
   
   anyway, i'm just wondering if there's a slick way of any sort to be able 
   to have a value generated from an INSERT available for immediate and 
   secure re-use.
   
   for example, if i have
   
   CREATE SEQUENCE foo_id_seq;
   CREATE TABLE foo (
   	id int2 primary key default nextval( 'foo_id_seq' ),
   	bar text
   );
   
   and then later have
   
   INSERT INTO foo( bar ) VALUES( 'baz' );
   
   is there any way safely to know what was inserted into the "id" field 
   without encapsulating the INSERT statement in a transaction with 
   whatever function needed that value?
   
   i'm guessing a transaction probably makes the most sense, right?
   
   e.g.,
   
   BEGIN WORK;
   INSERT INTO foo( bar ) VALUES( 'baz' );
   UPDATE foo SET bar = 'ola' WHERE id = ( SELECT last_value FROM foo_id_seq );
   END WORK;
   
   if so, then i guess the next question is: is there a preference between 
   a statement like
   
   UPDATE foo SET bar = 'ola' WHERE id = ( SELECT last_value FROM foo_id_seq );
   
   and
   
   UPDATE foo SET bar = 'ola' WHERE id = ( SELECT MAX( id ) FROM foo );
   
   thanks!
   
   -tfo
   
   
   

2. Re: simulating INSERT return values with default values

   Darren Ferguson <darren@crystalballinc.com> — 2002-07-22T19:36:43Z

   You can use currval function
   
   This takes the current value from the session you are using to connect to 
   the database. Provided you do not open another session then this should 
   suffice.
   
   However i would be inclined to use transactions just to be on the safe 
   side
   
   begin;
   insert into foo values ('tmp');
   select currval('foo_id_seq');
   commit;
   
   HTH
   
   On Mon, 22 Jul 2002, Thomas F. O'Connell wrote:
   
   > i didn't see this question asked (or answered in the mailing lists), but 
   > it seems like it would be an FAQ...
   > 
   > anyway, i'm just wondering if there's a slick way of any sort to be able 
   > to have a value generated from an INSERT available for immediate and 
   > secure re-use.
   > 
   > for example, if i have
   > 
   > CREATE SEQUENCE foo_id_seq;
   > CREATE TABLE foo (
   > 	id int2 primary key default nextval( 'foo_id_seq' ),
   > 	bar text
   > );
   > 
   > and then later have
   > 
   > INSERT INTO foo( bar ) VALUES( 'baz' );
   > 
   > is there any way safely to know what was inserted into the "id" field 
   > without encapsulating the INSERT statement in a transaction with 
   > whatever function needed that value?
   > 
   > i'm guessing a transaction probably makes the most sense, right?
   > 
   > e.g.,
   > 
   > BEGIN WORK;
   > INSERT INTO foo( bar ) VALUES( 'baz' );
   > UPDATE foo SET bar = 'ola' WHERE id = ( SELECT last_value FROM foo_id_seq );
   > END WORK;
   > 
   > if so, then i guess the next question is: is there a preference between 
   > a statement like
   > 
   > UPDATE foo SET bar = 'ola' WHERE id = ( SELECT last_value FROM foo_id_seq );
   > 
   > and
   > 
   > UPDATE foo SET bar = 'ola' WHERE id = ( SELECT MAX( id ) FROM foo );
   > 
   > thanks!
   > 
   > -tfo
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   > 
   > http://archives.postgresql.org
   > 
   
   -- 
   Darren Ferguson
   
   
   

3. Re: simulating INSERT return values with default values of sequences

   Andrew Sullivan <andrew@libertyrms.info> — 2002-07-22T20:09:19Z

   On Mon, Jul 22, 2002 at 02:18:54PM -0500, Thomas F. O'Connell wrote:
   > i didn't see this question asked (or answered in the mailing lists), but 
   > it seems like it would be an FAQ...
   
   In fact, it _is_ a FAQ, but you didn't know what you needed was
   currval().  Here's the entry:
   
   http://www.ca.postgresql.org/docs/faq-english.html#4.15.3
   
   A
   -- 
   ----
   Andrew Sullivan                               87 Mowat Avenue 
   Liberty RMS                           Toronto, Ontario Canada
   <andrew@libertyrms.info>                              M6K 3E3
                                            +1 416 646 3304 x110