Thanks for the help, it works great!
However, there is a problem with performance.
I am moving from MySQL to Postgres, and to test performance I am inserting a
large row (30 fields) into a table from my C program. I am running this
program 50 times, and timing the results. The MySQL version of the program
took 0.75 seconds to execute 50 times, but the Postgres version takes 22-25
seconds. A similar test with a simple select takes 3.5 seconds on Postgres
but 0.8 on MySQL. Postgres undoubtably has more features and is better for
my app than MySQL, but are these performance values normal?
All my program does is read the query from a text file, open the database
connection, perform the query, output currval('seqence_name') or the query
results to a text file, and close the connection. This is how my app needs
to work.
Thanks,
Mark.
>> Hi,
>>
>> I have written a C program to insert a row into a table with a
>> SERIAL column.
>>
>> Is there a way of returning the inserted value for this column
>> to my program? I.e. if there are rows with the serial column
>> for 1,2,3,4 and 5, and I insert a row, my program needs to be
>> told "6" for the new serial. There may be many instances of the
>> program running simultaneously so I can't do a "select max..."
>> or "select last_value..." workaround because by the time the
>> select is done, there may have been other rows inserted so the
>> last_value would be wrong. Also the program needs to be table-name
>> and column-name independent so that it can work for ANY insert
>> query into a table with a SERIAL column.
>
>Answer is that currval('seqence_name') will return your last sequence
>number, even if another session has assigned a sequence number since
>your nextval() call.