The Site With the Lamp

August 5, 2004

Sometimes when you inserting data into a table that has a generated id you find yourself wanting to know what the generated id was. Enter the last_insert_id() function of mysql.

When you enter data into a mysql table that has an auto_increment field you can then make use of this method call retrieve the identifier that was generated. If you happen to be using PHP the work is even easier you can retrieve this number with a call to the mysql_insert_id function of PHP.

At first glance it's counterpart in postgres seems to be the currval() function. Unfortunately appearences can be deceptive. The reason is that with last_insert_id() or the mysql_insert_id() you do not need to pass in any parameters - the database always returns the id associated with the last insert query that was excecuted. currval on the other hand takes the name of the sequence as a parameter.

Thus the only way to determine the relationship seems to be with an inspection of the database meta data - a cumbersome task at the best of times and takes quite a bit of effort and querying to get the task done. On the other hand as a programmer you already know the relationship between the sequence and the column so it's a simple matter to create your own function that can return the id for you. In fact it's a mere 5 lines of code.

CREATE FUNCTION add_user(char,char,integer) RETURNS BIGINT AS '
INSERT INTO users(userName,userPassword,userStatus)
VALUES($1,$2,$3);
SELECT CURRVAL(''users_userid_seq'');
' LANGUAGE SQL;


Instead of executing and Insert query as you might have done with mysql you do a SELECT when you want to add data to your table - to be more specific you might execute the following query:
SELECT add_user('raditha'.'123',1);

As with any other select you are presented with a resultset and it contains the value the value that was last generated by the sequence for the current connection to the data

Interestingly when talking to an MS sql server with JDBC you always get back a result set even with an INSERT query if the insert results in the auto increment field been updated. You can do the same by carrying out a select on @@id

Follow Up: see the mysql 2 pgsql migration guide