[lug] (OT) postgresql

[j davis]

>
>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

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail