[lug] (OT) postgresql

[Michael Deck]

I've had trouble with this syntax when performing the insert and the select within the same transaction. I've ended up using 

  select last_value from users_seq  

instead. It's not clear to me why this is so, and maybe a Postgres wizard can enlighten me. 

Also, if you're planning to do this a lot, you may want to create a stored procedure so you don't need to do two separate queries, e.g. 

      CREATE FUNCTION addUser (char(12)) 
      RETURNS integer as  +
        'INSERT INTO Users(user_name) values ($1); 
         SELECT last_value FROM users_seq'
      LANGUAGE 'sql'

I found this gave me a significant performance improvement over issuing the INSERT and then the SELECT from Java. 

-Mike

At 05:44 PM 11/10/2002, Sexton, George wrote:
>The way you should really do this is to create a sequence:
>
>CREATE SEQUENCE users_seq;
>
>CREATE TABLE Users (
>        user_id                         int NOT NULL PRIMARY KEY DEFAULT NEXTVAL('users_seq'),
>        user_name                       VARCHAR(12) NOT NULL,
>        ....
>
>to read the value just inserted:
>
>select CurrVal('users_seq') as newKey
>
>
>Select max() is incredibly bad. In a multi-user application there are no
>protections that two people won't each execute the select max(), and then
>perform their insert.
>
>It also will lock the table for the duration of the statement.
>
>Do not do select max().
>
>George Sexton
>MH Software, Inc.
>Home of Connect Daily Web Calendar Software
>http://www.mhsoftware.com/connectdaily.htm
>Voice: 303 438 9585
>
>
>-----Original Message-----
>From: lug-admin at lug.boulder.co.us [mailto:lug-admin at lug.boulder.co.us]On
>Behalf Of j davis
>Sent: 10 November, 2002 3:21 PM
>To: lug at lug.boulder.co.us
>Subject: [lug] (OT) postgresql
>
>
>
>Hello,
>  is there a postgresql select query i can enter to specify
>the last row of a table...i.e. i want to make a new invoice number based
>on the inv_num field of the last customer entry into a table.the only thing
>i can think off is to run a loop on the data base using perl to increment
>a var + 1 for every row in the table...then use this var to get a query
>to the last row...hope this is coherent :)
>
>thanks,
>jd
>
>jd at taproot.bz
>http://www.taproot.bz
>
>"Who you trying to get crazy with essay?
>          Don't you know I'm loco?"
>
>-Cypress Hill
>
>
>
>_________________________________________________________________
>Add photos to your e-mail with MSN 8. Get 2 months FREE*.
>http://join.msn.com/?page=features/featuredemail
>
>_______________________________________________
>Web Page:  http://lug.boulder.co.us
>Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
>Join us on IRC: lug.boulder.co.us port=6667 channel=#colug
>
>_______________________________________________
>Web Page:  http://lug.boulder.co.us
>Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
>Join us on IRC: lug.boulder.co.us port=6667 channel=#colug


Michael Deck
Cleanroom Software Engineering, Inc.