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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
