[lug] (OT) postgresql

[Michael Deck]

At 08:56 AM 11/11/2002, you wrote:


>>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
>
>
>
>
>heres what i wound up with....using perl
>
>
>$result = $conn->exec("INSERT INTO cust1 VALUES((select nextval('new_num')), '$date', '$in{x_method}', $quanity, '@array', $ship_cost, $insure, $cost_less_ship, $total, '$in{x_first_name}', '$in{x_last_name}', '$in{x_address}', '$in{x_city}', '$in{x_state}', $in{x_zip}, '$in{x_country}', '$in{x_phone}', '$in{x_email}', '$in{x_ship_to_first_name}', '$in{x_ship_to_last_name}', '$in{x_ship_to_address}', '$in{x_ship_to_city}', '$in{x_ship_to_state}', $in{x_ship_to_zip}, '$in{x_ship_to_country}')");
>
>jd

I don't know the perl bindings (in fact, I don't know perl) but I don't *think* that $result will contain the new primary key created for the record -- it will contain the object ID (OID) of the record instead, not at all the same thing. So if you are expecting to follow this with a function that uses $result to select into custl, you'll be disappointed. 



Michael Deck
Cleanroom Software Engineering, Inc.