Thread: Insert Data and autonumeric field

Hi everybody,I need to insert a row in a table and get the Id of this row ( My primary key ).
Example:
    INSERT INTO table1 (date, field2, field3) VALUES (now,'value2','value3');    SELECT last_value FROM seq_table1;
I'm running each command apart. My application retrieves the last_value and uses it in another command:
    INSERT INTO table2 (pk1, field1, field2, field3) VALUES ( last_value_variable, 'value1','value2','value3');


PROBLEM: Many clients are getting duplicated IDs.

What is the best way of doing this?

I tried a function:

CREATE OR REPLACE FUNCTION fu_insertrow(int4, text) RETURNS int4 AS
$BODY$
DECLARE   i_lastvalue INTEGER;
BEGIN
    INSERT INTO table1 (date, field1, field2) values (now(),$1,'$2');    SELECT  i_lastvalue INTO i_lastvalue from
"seq_ChamadaId";   RETURN i_lastvalue; 

END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;



Using this function:

SELECT fu_insertrow(value2, value3);

The app gets the return value of the function above and uses it in my insert:    INSERT INTO table2 (pk1, field1,
field2,field3) VALUES ( function_return_variable, 'value1','value2','value3'); 

Suggestions?

Thanks,André Guergolet

On Thu, Feb 01, 2007 at 04:09:22PM -0300, André José Guergolet wrote:
> PROBLEM: Many clients are getting duplicated IDs.
>
> What is the best way of doing this?

Use a sequence.  You can get the current value of the sequence with
SELECT currval('seqname').  No, there's not a race condition; see the
docs.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Information security isn't a technological problem.  It's an economics
problem.    --Bruce Schneier

André José Guergolet <AGuergolet@compugraf.com.br> writes:
>         INSERT INTO table1 (date, field2, field3) VALUES (now,'value2','value3');
>         SELECT last_value FROM seq_table1;

You should never ever look directly at the sequence table (except
perhaps for manual debugging purposes).  I think you may have confused
this with use of the lastval() function --- currval() or sometimes
lastval() are the appropriate way to get the last-assigned value.
        regards, tom lane